SQL return 1 row in results for each column in table

Question

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

Possible duplicate: http://stackoverflow.com/questions/19055902/unpivot-with-column-name — Mahedi Sabuj, Jun 21 '16 at 19:39
@MahediSabuj Thanks, it absolutely is the exact same issue. It seems to me the title of that question could be elaborated on, currently it's like needing to know the answer to the question in order to find the question. Definitely tried googling a lot of different things and never found that one. Thanks for the help. — Steve Eggering, Jun 21 '16 at 19:56
I don't have enough reputation to comment on the answers of that other question, but I'd like to know if there is a way to include NULL values. — Steve Eggering, Jun 21 '16 at 20:16

score 4 · Accepted Answer · answered Jun 21 '16 at 19:39

4

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);

answered Jun 21 '16 at 19:39

Lamak

69,480
12
108
116

This actually does the best job! Matt's method worked well, but it was having trouble with some bit columns. – Steve Eggering Jun 21 '16 at 21:38

score 3 · Answer 2 · answered Jun 21 '16 at 19:44

3

Using UNPIVOT the query becomes even simpler. with UNPIVOT the query would look something like....

Select Users 
     , Q 
     , V
FROM TableName
  UNPIVOT (
            V FOR Q IN (Q1,Q2,Q3,Q4)
           )up

answered Jun 21 '16 at 19:44

M.Ali

67,945
13
101
127

@Matt can I ask you the same question ? – M.Ali Jun 21 '16 at 19:47
@Matt not that there's much difference, but what's the point of using `SELECT * FROM (SELECT * FROM Tablename)...` instead of just `SELECT * FROM Tablename ....`? – Lamak Jun 21 '16 at 19:48
I did not see your answer at all until I have had posted mine, its says 4 min next to both of ours answers – M.Ali Jun 21 '16 at 19:48
I know M.Ali I have done that a few times myself for me it shows that I am a minute ahead of you so it is probably just seconds then :) we will just leave it as great minds think alike :) – Matt Jun 21 '16 at 19:50
@Matt So, you are saying this answer is wrong?, since it's not using the "inner select statement" – Lamak Jun 21 '16 at 19:54
@Matt I know this answer is not wrong, but you said that both `SELECT`s were needed when they are not – Lamak Jun 21 '16 at 19:57
Matt and M. Ali both answers do the job cleanly, and I see a 1 minute difference in the answered at times, I'm sure you were both typing at the same time, but "tie goes to the runner" or something like that, so M. Ali gets the green check mark today. – Steve Eggering Jun 21 '16 at 20:05
@M.Ali any way to include nulls? – Steve Eggering Jun 21 '16 at 20:18

score 2 · Answer 3 · edited May 23 '17 at 10:28

make sure the value used to eliminate null is not represented in your dataset!

SELECT
    [user]
    ,V = IIF(V = -99999,NULL,V)
    ,Q
FROM
    (
    SELECT
       [user]
       ,Q1 = ISNULL(t.Q1,-99999)
       ,Q2 = ISNULL(t.Q2,-99999)
       ,Q3 = ISNULL(t.Q3,-99999)
       ,Q4 = ISNULL(t.Q4,-99999)
    FROM
       @Table t) p

    UNPIVOT
       (V FOR Q IN (Q1,Q2,Q3,Q4)
       ) as unpvt

To allow for null's per your comment, have to use a hack to set a null value as something else and put it back to null. (SQL Server - Include NULL using UNPIVOT) When wanting to include the cross apply method becomes a little more attractive to me.

Seems to me UNPIVOT would work great for you seeing you have a known number of quarters.

And for reference here is the link to Mirosoft's site on PIVOT and UNPIVOT. https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

http://stackoverflow.com/questions/1002989/sql-server-include-null-using-unpivot seeming that you might want to use @Lamak cross apply if you want nulls because you will have to hack it a little to get it with unpivot but I will update my answer with a way to do it. — Matt, Jun 21 '16 at 20:25

SQL return 1 row in results for each column in table

3 Answers3

Linked