Search code examples
sqlsql-servert-sqlssms-2014

SQL return 1 row in results for each column in table

Admittedly I'm not good with pivot/unpivot, but it seems like that relates to a more direct "transpose" than what I'm looking for. What I need to do is create a results set with 1 row for each of a set of columns in a table.

Quarters Table:

USER    Q1  Q2  Q3  Q4
-----------------------
USER1   1   5   3   3
USER2   2   1   1   8

Desired Results:

USER    Q   V
--------------
USER1   Q1  1
USER1   Q2  5
USER1   Q3  3
USER2   Q4  3
USER2   Q1  2
USER2   Q2  1
USER2   Q3  1
USER2   Q4  1
Solution

  • A "funny" way of do it would be using CROSS APPLY:

    SELECT  Q.[USER],
        X.Q,
        X.V
FROM dbo.Quarters Q
CROSS APPLY 
(
    VALUES
        ('Q1', t.Q1),
        ('Q2', t.Q2),
        ('Q3', t.Q3),
        ('Q4', t.Q4)
) X (Q, V);