x

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

more ▼

asked Jul 25 at 01:37 PM in Default

avatar image

David 2 1
1.3k 56 61 69

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

 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

more ▼

answered Jul 25 at 07:31 PM

avatar image

Oleg
19k 3 7 28

Fantastic. Thank you very much.

Jul 29 at 03:01 PM David 2 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x439
x402
x115

asked: Jul 25 at 01:37 PM

Seen: 80 times

Last Updated: Jul 29 at 03:01 PM

Copyright 2017 Redgate Software. Privacy Policy