question

vivekyadav0212 avatar image
vivekyadav0212 asked

Pivot Data using T-SQL and SSIS

Hello Everyone, I m looking for ways to pivot data using T-SQL script, SSIS and SSRS. Below attached is the input table and output required after pivoting the data. ![alt text][1] [1]: /storage/temp/1293-pivot.jpg I m able to achieve this using SSRS, but please if you can help me in getting this using T-SQL and SSIS. Also let me know if I can use script component in SSIS.
ssispivotsql-server-7
pivot.jpg (73.7 KiB)
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mister Magoo avatar image Mister Magoo commented ·
Can I ask what you are trying to achieve, if you can already do this in SSRS, why the need to avoid that and specifically use a script task in SSIS? When someone asks a question like this it is often either homework or a self-imposed restriction that isn't usually neccessary... Can you expand on the problem you are trying to solve?
1 Like 1 ·
vivekyadav0212 avatar image vivekyadav0212 commented ·
I agree with you. It is actually a self imposed restriction. The actual problem is only as mentioned above, but I m trying to achieve this in different ways. Will be thankful if you suggest or help me out on this.
0 Likes 0 ·
Scot Hauder avatar image Scot Hauder vivekyadav0212 commented ·
What if you have 50 products in one category, you want a 156 column wide report? Also, in the output you have no way of knowing which product goes with what values. Don't waste your time on this.
1 Like 1 ·
vivekyadav0212 avatar image vivekyadav0212 vivekyadav0212 commented ·
I agree with you Scot :). I have 200 records and will have to create csv from output. I know this problem statement looks weird and same way I also felt when I got this. But no issue, I tried this in SQL and able to achieve the output. Thanks Mister, Scot :)
0 Likes 0 ·

1 Answer

·
@SQLShark avatar image
@SQLShark answered
In T-SQL you could do a pivot of sorts. SELECT IT.Category, MAX(CASE WHEN IT.PRODUCT = 'PA1' THEN IT.Price END) AS 'PA1_Price', MAX(CASE WHEN IT.PRODUCT = 'PA1' THEN IT.Quantity END) AS 'PA1_Quantity', MAX(CASE WHEN IT.PRODUCT = 'PA1' THEN IT.SalesPerc END) AS 'PA1_SalesPerc' FROM INPUT_TABLE IT GROUP BY IT.Category You would then just need to add in the additional columns. This isn't a good solution though, it is very limited. If you have more products then this will not work unless you make it dynamic with an SP or something similar.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vivekyadav0212 avatar image vivekyadav0212 commented ·
Yes, the only problem is that it has over 200 products :) I m able to prepare the script for this to achieve the result. Thanks for this solution SQLShark.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.