I have a table with following structure:
ProductID OrderType Value
1001 BaseSales 2000
1001 Incremental 1000
1001 TotalSales 3000
1002 BaseSales 2002
1002 Incremental 1003
1002 TotalSales 3005
I would like to get the data in following format:
ProductID BaseSales Incremental TotalSales
1001 2000 1000 3000
1002 2002 1003 3005
Hi Guys, wondered if anyone could point me in the right direction with this one.
I'm fairly new to SSIS and one data flow transformation i'm trying to get my head around is the pivot transformation. Perhaps if i explain what i want it to do it will make my problem seems clearer.
I have a table which contains a list of people and their demographic and personal details, each with a unique identifier number. When each persons details are entered one of the pieces of data captured is the month they were registered and their ethnic group classification. Within my data flow i want to take this table of registrations and pivot it like you would in Excel e.g. take the whole list, add months as the columns and ethnic categories as the rows and then a count of the unique identifiers as the value, giving you a simple count of all of the customers and what different ethnic groups per month were registered. Simple enough??
Now, i've tried to do this using the pivot and i seem to be getting lost so after reading the chapter from my wrox book i've tried to outline how i think it should be configured to see if it makes anymore sense, i'd appreciate it if anyone could point out where i might be going wrong.......
In my example i'm assuming my input columns will be the unique identifier, the ethnic group and the month?
I'll then select the ethnic group as the row and the month as the pivot and the unique code as the value using the pivotusage properties of 1, 2, 3. still on the right track??
I then create an output column of each month and set the pivotkey value to the month name and the lineageID to the ID of the unique identifer column.
Can anyone spot if i've missed something out here?
Does anyone have any ideas how to resolve this problem?
I have set my PivotKey to be a Column containing the ColumnNames which I want to Pivot. I have taken the LineAgeId value from the Input column and used it as my SourceColumnID in the Ouput Columns tab.
Error 1 Validation error. Data Flow Task: Pivot [231]: Output column "ATTRIBUTENAME" (489) cannot be mapped to PivotKey input column. Package.dtsx 0 0
I am using the pivot component in SSIS to pivot a set of data, and up until now it has worked fine. However lately it has started to produce duplicates, meaning 2 rows for a combination of the keys (type 1) used. It is completely random, meaning a combination of keys for one run can work correctly, and in the next run it inserts an extra record for the combination for just one of the pivoted values. This also means that for a batch of 10 mill rows input the output is completely random, because the number of duplicates vary.
Are there any memory restrictions or similar that causes this, or do you guys have any idea what might be wrong?
I have an Issue. Please find the sample of my data source
StudentID Fee 1 Fee 1 Currency Type Fee2 Fee 2 Currency Type
1 10 USD 20 INR
2 45 EUR 20 USD
If I need to transform this data, it should be like below Table.
ID StudentId FeeType FeeAmount CurrencyType
1 1 Fee 1 10 USD
2 1 Fee 2 20 INR
3 2 Fee 1 45 EUR
4 2 Fee 2 20 USD
I have selected columns Fee 1 and Fee 2 in Unpivot Transformation. I set the "FeeAmount" as Destination Column Name and "FeeType" as Pivot Key Value Column Name. I left the Pivot Key Value as is for all the selected columns. So far no issue that I am able to get the Fee Type and Fee Amount columns for my destination table.
But the issue is how to get the Currency Type for each corresponding coulmns? I was not able to get the currency type for each student. I hope you got my point. Please let me know If have further queries and hel me out to find this.
I am importing user id's from CSV file into my Database. The source user id is of 4 character long and the destination user_id field is of 100 varchar. I just query the data so that i wan'a check that is said user id is already exist in the database or not . So i got this error. Read the script and error below '************************************************* ********************* ' Visual Basic Transformation Script '************************************************* ***********************
' Copy each source column to the destination column Function Main()
DIM v_user_id,rs, ConStr,sql set rs = CreateObject("Adodb.Recordset") set con = CreateObject("Adodb.Connection")
ConStr = "Provider=SQLOLEDB;Server=192.168.1.71;Database=tes tkaanza;uid=sa;pwd=sa" v_user_id = DTSSource("Col001") rs.open "Select user_id from tbl_gc_user_hdr where user_id = " + v_user_id,ConStr,0,1
if Not rs.eof then MsgBox "Record Found" else MsgBox "Record Not Found" end if rs.Close End Function
i got error from this line : rs.open "Select user_id from tbl_gc_user_hdr where user_id = " + v_user_id,ConStr,0,1
Error message :
Error Source: Microsoft OLE DB Provider for SQL Server Error Description:The conversion of the varchar value '1000015151910165' overflowed an int column. Maximum integer value exceeded. Error Help File: Error Help Context ID:0
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging. But I didnt find any docs/papers/books on it.
Plz help. Shigs. ============================= Are there those, In this world of brave, Who can tell me, How should I behave, When I am disgraced. =============================
Hi Gurus, I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform. In the 'Use Results of an SQL Query' pane of Lookup Transform:
Code SnippetEXEC GetMonthlyDataExtract 4, 2007
( I am passing month and year values) this works ok.
But when I chage to
Code SnippetEXEC GetMonthlyDataExtract ?, ?
It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters. Also I am interested to map parameters to variables not to input columns. If mention if that is not possible or any other alternative.
So this has got to be considered a major, major flaw in how SSRS interacts with Oracle. I'm using the "Oracle" data provider, but I've also tried using Microsoft's OLE DB data source, and some others, and in no case does SSRS hand off to Oracle a query that does NOT have bind variables. In other words, typically query parameters get passed off to Oracle as bind variables.
The incredibly major problem that this causes is that it disallows Oracle's use of star transformation queries which is the primary method by which to get fast responses to a data warehouse/star schema, in fact a prime authority on this subject (Bert Scalzo, Oracle DBA Guide to Data Warehouse and Star Schemas, p.86 -- obvioulsy not using Oracle 7x was the first) lists it as in effect the #1 consideration.
So what gives? In effect SSRS cannot be used against large scale Oracle data warehouses? I've had success with Business Objects being able to access Oracle star transformations.
So a guess my question is how the heck can use SSRS in a big, Oracle-based data warehouse?
For star_transformation join plans, the following parameters must also be considered: ... No BIND VARIABLE in SELECT statement
http://www.orafaq.com/usenet/comp.databases.oracle.server/2003/09/28/2305.htm Star transformation is not supported for tables with any of the following characteristics: * Queries that contain bind variables
consider the following table: name , TaskDate john , 01/01/2006 john , 01/03/2005 steve , 01/05/2006 i want to build a select statement that gives result like the following: name , JanuaryTotal , FebruaryTotal john , 150 , 110 steve , 170 , 50 so the result will be total tasks in specific month, is it doable in one select or should i turn around using hash tables ???
My first post here. I have an Analysis Services Cube and I need to export some of its data into a flat file (a semicolon separated value file) with fixed length columns.
What I have in Integration Services now is:
DataReader Source (ADO.Net conection) Data Transformation (NTEXT -> WSTR) Derived Column (WSTR -> STR, and some ISNULL validation) Flat File Destination (Delimited by ";")
the thing is I have all the measures and calculated measures in the cube formated with format strings and they work fine when I do a query through the SQL server Managment Studio, but in Integration Services before the columns are written into the file they lose its format.
for example, I have a calculated member called "Deuda Total Nacional" that returns something like this +0001234,00 and I need to take off the decimal separator so it is written into the file like this +000123400 , I'm doing it this way
(ISNULL([Deuda Total Nacional]) ? "+00000000000" : REPLACE([Deuda Total Nacional],",",""))
In my package I am using lookup to get new and similar record. I want to filter the rows for Lookup Reference Data Set by using Variable Value.
I have created variable @[User::CustId] with Int32 datatype, having default value 2 when I am trying to evaluate below query I am getting errorÂ
"select CustId,PartNm,LocId,LocTyp from loc where CustId= "+ @[User::CustId]Â
Error. The Data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+".
The operand types could not be implicitly cast into compatible types for the operation. To perform this operation , one or both operands need to be explicitly cast with the operator.
hi there, I have never use DTS before, now I am reading textbook for some special demand with DTS the textbook not talk very much for the detail of skills. seems the easy way to finish this query is using DTS wizard. but my requirement seems can't be done by DTS wizard. here are my requirement below. [move online Database to offline Database ] 1. the time of data preserve will have to reference separate firm's history data backup time ( for example, A company used to preserve data 6 months, and B company used to preserve data 12 months and so on..) 2. we will have only 2 kind of preserve time one is 6 months another is 12 months 3. The online DB only keeps 6 months data ( for example, when we do the DTS on 11/1 , we will only keep the data which from 5/1~10/31) , all data have to move to off-line DB except the past 6 months data 4. We will have to reference the history data preserve time to delete data after finished data movement those requirement looks very diffcult for me because I have never use DTS before , can you please give me a simple example or maybe some article I can reference?
Hi, I have the following query that kinda does what i want
SELECT ABTANumber, TourOperator, ReportStatus, COUNT(*) AS Counter FROM (SELECT ABTANumber, TourOperator, r.ReportStatus FROM bookingdetails bd LEFT JOIN report r ON bd.Id = r.BookingDetailsId) a GROUP BY ABTANumber, TourOperator, ReportStatus
SELECT [R].[PaymentMonth], [S].[RegionCode], [S].[CmsStateShortName], [P].[Attribute1] AS [FinalProduct], [Membership] = SUM([R].[Membership])
FROM [RptMMRSummary1] [R] INNER JOIN [RefCmsState1] [S] ON [R].[CmsStateCode] = [S].[CmsStateCode] INNER JOIN [RefPlanBenefitPackage1] [P] ON [R].[PlanBenefitPackageID] = [P].[PlanBenefitPackageID] WHERE [R].[PaymentMonth] IN ('200712', '200711', '200612') -- [P].[Attribute1] IN ('HMO', 'PPO', 'PFFS', 'SNP', 'EVCSNP') GROUP BY [R].[PaymentMonth], [S].[RegionCode], [S].[CmsStateShortName], [P].[Attribute1] ------------------------------------------------------------------- How do we use the pivot query for the above script. Layout as below
200801 Month / Year Selection
------------------------------------------------------- StateShortname | Attribute | Attribute | Attribute | Total ---------------------------------------------------------- AL Values Values Values OL Values Values Values ZW Values Values Values WEST Group By Region Code Total BK Values Values Values MN Values Values Values EAST Group By Region Code Total
200712 Always Previous Month for the above selection Month /Year
------------------------------------------------------- StateShortname | Attribute | Attribute | Attribute | Total ---------------------------------------------------------- AL Values Values Values OL Values Values Values ZW Values Values Values WEST Group By Region Code Total BK Values Values Values MN Values Values Values EAST Group By Region Code Total
200612 Always Previous year End Month for the above select Month / Year
------------------------------------------------------- StateShortname | Attribute | Attribute | Attribute | Total ---------------------------------------------------------- AL Values Values Values OL Values Values Values ZW Values Values Values WEST Group By Region Code Total BK Values Values Values MN Values Values Values EAST Group By Region Code Total
I would like to have it in the following format ======================================================== unitid CCT1 CCT2 CCT3 CCT4 ---------------------------------------------------------------------------------------------------------------- 1 Ravi,Raja,Kanna Senthil,Lee,Suresh 2 John,Vijay,Nithya Ram,Krish,Latha Raja,Vijay,Ram Sankar
Hi! I have a table Items(id, name, section, id_format, price) and a table Format(id, format,order) What I want to do is display: Name, Format1, Format2, Format3. How can I acheive that result?? THanks!! -JTM
(SQL Server 2005) I have the following query (trying to execute in Managment Studio):
SELECT Rate, Lender, Pricing, Max(Pricing) AS Maximum, Min(Pricing) As Minimum, Avg(Pricing) As Median FROM [10_Tier].[dbo].[Temp10Tier_1000] PIVOT (Sum(Pricing) For Lender)
I get the error: Incorrect syntax near ')' Microsoft SQL Server Error 102
I've been looking all over and have found many examples but I cannot get any of them to work.
What I want to accomplish is to be able to create a pivot query and use it in the "Query Builder" when designing a report for the reporting services, so that I can have a table that mimicks a "Matrix" - so that I can have multiple value columns.
is it possible to do a pivot , where the number of columns is dynamic...i.e
i dont know how many rows will be selected , and i want to pivot them and insert into
a new (temp/tabletype)table...obv i dont know how many columns i need....
somethin like the example of books online pasted below , consider here that i need data for
all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid
from emp' in the pivot syntax 'FOR EmployeeID IN' .
pls tell me if such thing is possible or there is a turnaround for my problem...
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] ) ) AS pvt ORDER BY VendorID
Is it possible to create a named query in the DSV that is the result of a pivot (e.g. cross tab?). The number of columns as a result of the pivot are based the number of records in one of the driving tables - in other words, it is not fixed.
Can someone help me parsing this ms-access PIVOT sql-statement to ams-sql-server sql-statement?Many thanks in advanceTRANSFORM Count(KlantenStops.id) AS AantalVanidSELECT KlantenStops.Uitvoerder, KlantenStops.KlantFROM KlantenStopsGROUP BY KlantenStops.Uitvoerder, KlantenStops.KlantPIVOT DatePart("m",leverdatum,1,0) In("1","2","3","4","5","6","7","8","9","10","11","12");
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt
I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
Is there any restrictions in the number of rows that will be returned when doing a query in PowerBI? I have a query which should return over 1 million rows but the in PowerBI I only seem to get around 57000.
