Select multiple columns into multiple rows

Question

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

Gordon Linoff · Accepted Answer · 2018-01-26 23:04:19Z

6

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.

answered Jan 26, 2018 at 14:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

From your perspective is this approach better than just repeated union statements:

Or better yet, could i approach this by building a "Column" table with all the columns i want to select and utilize sys.tables and sys.columns?

I accepted as is, this works for what I need. I'll just have 78 value pairs. Thank you sir.