mikegrad7
asked on
Need help with TSQL - Pivoting columns using PIVOT or something else
i have data coming from a large query that needs to be transposed. Piviot might not work in this instance, and either way i havent been able to get it to work. This cannot be done in SSRS, it has to be straight from the DB, output as a table for another application to use.
this result is currently generated by dynamic sql, as the USERCURR columns is dynamic based on the calling app. lets say there are columns USERCURR1 through USERCURR10. Not all columns will have data.
The Original table will have some static columns that need to remain, and then instead of the USERCURR columns, they are replaced with two columns, BUCKETNAME and BUCKETAMT. For each row, it get SPLIT into multiple rows based on the number of USERCURR columns. I have attached a spreadsheet which clearly explains the need.
example-for-ee.xlsxexample-for-ee.xlsx
Ideally, if the USERCURR column is null or 0 we would not include it, but thats ok as it could be cleaned up later
this result is currently generated by dynamic sql, as the USERCURR columns is dynamic based on the calling app. lets say there are columns USERCURR1 through USERCURR10. Not all columns will have data.
The Original table will have some static columns that need to remain, and then instead of the USERCURR columns, they are replaced with two columns, BUCKETNAME and BUCKETAMT. For each row, it get SPLIT into multiple rows based on the number of USERCURR columns. I have attached a spreadsheet which clearly explains the need.
example-for-ee.xlsxexample-for-ee.xlsx
Ideally, if the USERCURR column is null or 0 we would not include it, but thats ok as it could be cleaned up later
:) this is an UNpivot, however the code I'm proposing does not use the 'unpivot' feature, instead it uses a different technique which is very efficient to achieve this type of result. It uses a combination of cross apply and 'values'.
The result of this code applied to your sample is:
there are further references for this technique here. Note this technique is marginally faster than the 'unpivot' (elapsed) and consumes less cpu (results An Alternative (Better?) Method to UNPIVOT (SQL Spackle) By Dwain Camps, 2012/08/02)
As you can see this isn't dynamic code, but it can be made into dynamic.
{+edit copy/paste problem - fixed}
The result of this code applied to your sample is:
FUND INVESTOR OPERATIONNUMBER BUCKETNAME BUCKETAMT
Fund 1 Inv1 1 USERCURR1 1234
Fund 1 Inv1 1 USERCURR2 237
Fund 1 Inv2 1 USERCURR15 2789
Fund 1 Inv2 1 USERCURR16 1298
Fund 1 Inv3 1 USERCURR2 986
Fund 1 Inv3 1 USERCURR16 432
Fund 1 Inv1 2 USERCURR1 3266
Fund 1 Inv1 2 USERCURR2 674
Fund 1 Inv1 2 USERCURR15 3456
Fund 1 Inv2 2 USERCURR1 6789
Fund 1 Inv2 2 USERCURR15 12567
Fund 1 Inv2 2 USERCURR16 7841
The SQL for this is:
SELECT
Fund
, Investor
, OperationNumber
, BUCKETNAME
, BUCKETAMT
FROM original
CROSS APPLY (
VALUES
('USERCURR1', USERCURR1)
, ('USERCURR2', USERCURR2)
, ('USERCURR15', USERCURR15)
, ('USERCURR16', USERCURR16)
) AS CrossApplied(BUCKETNAME , BUCKETAMT)
WHERE bucketamt IS NOT NULL
this may be seen/used at http://sqlfiddle.com/#!3/b9a0f/1there are further references for this technique here. Note this technique is marginally faster than the 'unpivot' (elapsed) and consumes less cpu (results An Alternative (Better?) Method to UNPIVOT (SQL Spackle) By Dwain Camps, 2012/08/02)
As you can see this isn't dynamic code, but it can be made into dynamic.
{+edit copy/paste problem - fixed}
oh, I see I have missed a bit, the 'line identifier' - sorry - back soon I hope
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PortletPaul's example reminded me of some key points:
- Ensure the columns have the same data types and length. If not, you may want to have a derived table or common table expression handle CONVERT before you denormalize the data.
- Confirm the value in the empty columns is really NULL and not zero or empty string. If either of the latter two, then you can again use an intermediary query to massage the data before you UNPIVOT.
- Ensure the columns have the same data types and length. If not, you may want to have a derived table or common table expression handle CONVERT before you denormalize the data.
- Confirm the value in the empty columns is really NULL and not zero or empty string. If either of the latter two, then you can again use an intermediary query to massage the data before you UNPIVOT.
Excellent points, yes there is an inherent assumption your can "stack" these values into a column hence they need to share data type. Good catch mwvisa1.
(do I detect some experience there?)
(do I detect some experience there?)
actually, you could also do some tidy-up in the cross apply if needed, e.g.
CROSS APPLY (
VALUES
('USERCURR1', convert(decimal (18,2),USERCURR1)) -- e.g.
, ('USERCURR2', USERCURR2)
, ('USERCURR15', USERCURR15)
, ('USERCURR16', USERCURR16)
) AS CrossApplied(BUCKETNAME , BUCKETAMT)
CROSS APPLY (
VALUES
('USERCURR1', convert(decimal (18,2),USERCURR1)) -- e.g.
, ('USERCURR2', USERCURR2)
, ('USERCURR15', USERCURR15)
, ('USERCURR16', USERCURR16)
) AS CrossApplied(BUCKETNAME , BUCKETAMT)
ASKER
wow, you guys are fast, i need to dig thru these and see what works.
FYI, the line number in the spreadsheet is just for clarity, its not part of the data set.
I am still a bit confused on how i keep the initial columns the same, but ill run some tests.
Thanks
Mike
FYI, the line number in the spreadsheet is just for clarity, its not part of the data set.
I am still a bit confused on how i keep the initial columns the same, but ill run some tests.
Thanks
Mike
ASKER
one other comment, my original table is actualyl the result of some dynamic SQL. Im having trouble getting that into a temp table due to the dynamic SQL issues, and then accessing that table later. I know it adds a bit of a wrinkle. Right now i am going to try and as my dynamic sql as the original table part
Ah, do you not have access to edit the original dynamic SQL? The reasoning behind my showing you the UNPIVOT syntax is that you probably already have a variable in the dynamic SQL code with the column names, which you said is variable. Leveraging this columns variable, you can have the dynamic SQL pass you the table already denormalized.
If no, how are you getting the dynamic SQL results? Is it from a stored procedure?
If no, how are you getting the dynamic SQL results? Is it from a stored procedure?
ASKER
no, i have the dynamic SQL string, i was trying to insert the results to a temp table, inside a stored proc, but a few tests have shown that i can do this whole thing in one step, with the sqyamic sql select statement after the from ( sql) as original
ASKER
this help was fantastic. I have things working as expected.
ASKER
Thanks to everyone and PortletPaul with the best answer! I have this working now.
The dynamic SQL ended up looking something like this
then i just execute(@sql1).
The dynamic SQL ended up looking something like this
select @sql1 = '
SELECT
OPERATION_ID , EFFECTIVE_DATE ,
APPROVED
FROM
( SELECT A.IQID as OPERATION_ID,A.CLOSEDATE as EFFECTIVE_DATE,A.USERBOOL1 as APPROVED,A.DRAFT as DRAFT,' + @bucket_list_string + 'FROM (someplace) ) as original
CROSS APPLY ( VALUES ' + @split_bucket_string + ' ) AS CrossApplied(BUCKETNAME , BUCKETAMT)
WHERE bucketamt IS NOT NULL order by SOMEVALUE'
then i just execute(@sql1).
thanks!, and good work making it dynamic. Cheers, Paul
Essentially:
Open in new window
In your dynamic SQL, you could do something like this:
@sql = '...
UNPIVOT (
BUCKETAMT
FOR BUCKETNAME
IN (' + @columns + ')
) upvt
...'
I hope that helps!