question

David 2 1 avatar image
David 2 1 asked

How to UNPIVOT

Hi there, What would be the query to UNPIVOT this table? userID tControl1 longTextControl1 1277 WEWE WEWE 1277 dsdsds NULL 1277 ewewew wewe 1277 dfdfd dsfsdfs 1277 sdsds sdsds 1277 fgfgdfg fgdfg TIA
sql-server-2012tsqlpivot
10 |1200

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

1 Answer

·
Oleg avatar image
Oleg answered
I am sure that the similar question has been answered many times before, but here is another take on it, just because I want to show a different approach in addition to the standard unpivot. There are 2 popular ways to unpivot the data: - [unpivot][1] - cross apply values Here is the sample script which uses unpivot: select UserID, ColumnName, ColumnValue from YourTable scr unpivot (ColumnValue for ColumnName in ([tControl1], [longTextControl1])) upt; Here is the sample script which uses cross apply values: select UserID, ColumnName, ColumnValue from YourTable cross apply ( values ('tControl1', tControl1), ('longTextControl1', longTextControl1) ) x(ColumnName, ColumnValue); There is a very important difference between these 2 methods: The unpivot will not include the rows with NULL for unpivoted value, but cross apply values will. So, from 6 rows of sample data in question, unpivot will produce 11 rows (excluding the data from row 2 which has NULL in longTextControl1 column) while cross apply values will return all 12 rows. In case if there is a need to guarantee 2 unpivoted records for each row in the input, the second method appears to be more suitable. Hope this helps. Oleg [1]: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot
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.

David 2 1 avatar image David 2 1 commented ·
Fantastic. Thank you very much.
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.