What would be the query to UNPIVOT this table?
asked Jul 25, 2017 at 01:37 PM in Default
David 2 1
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:
Here is the sample script which uses unpivot:
Here is the sample script which uses cross apply values:
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.
answered Jul 25, 2017 at 07:31 PM