CODEWITHSUNDEEP
sqlsql-servert-sql
RyanC
RyanC

Reputation: 13

Select multiple columns into multiple rows

UserID  | UserName  | 534 more columns -->
1       | John      | 534 more values  -->

I'm looking for this output from only about 78 of these columns:

Column  | Value 
UserID  | 1
UserName| John
+78 more rows

Upvotes: 0

Views: 1953

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is called unpivoting and I like to use apply:

select v.*
from t cross apply
     (values ('UserId', t.UserId),
             ('UserName', t.UserName)
             . . .
     ) v(column, value);

Do note that this assumes that all the columns have the same type.

apply implements what is technically called a "lateral join". There are other ways to implement this logic -- using union all or unpivot.

However, lateral joins are quite powerful and unpivoting is a good introduction to using them.

Upvotes: 6

Related Questions