How can I unpivot multiple columns into name and value?

Question

I have a table like this one:

Dates   Time    Item1   Item2   Item3
1-Jan   0:00    Bread   Bread   Peach
1-Jan   6:00    Lemon   Peach   Peach
1-Jan   12:00   Lemon   Bread   Bread
1-Jan   18:00   Bread   Lemon   Peach
2-Jan   0:00    Bread   Lemon   Peach
2-Jan   6:00    Bread   Lemon   Peach
  …      …        …       …       …

And I want to achieve this one:

Dates   Times   Item    Food
1-Jan   0:00    Item1   Bread
1-Jan   0:00    Item2   Bread
1-Jan   0:00    Item3   Peach
1-Jan   6:00    Item1   Lemon
1-Jan   6:00    Item2   Peach
  …      …       …       …

I know how to unpivot one column, the problem is when I try to unpivot all of them at the same time, I do not know how to make the union properly

score 1 · Answer 1 · edited Dec 09 '18 at 19:30

1

Would this give the same result than cross apply?

SELECT Dates, Time, Item, Food
From mytable
unpivot (
Food
for [Item] in ([Item1],[Item2],[Item3])
)AS p

Rextester Demo

edited Dec 09 '18 at 19:30

Barbaros Özhan

59,113
10
31
55

answered Dec 07 '18 at 17:17

zhivab

99
11

no. Its another answers to your question and this is the other possibility. – junketsu Dec 07 '18 at 19:33

LukStorms · Answer 2 · 2018-12-07T19:42:27.083

0

You can CROSS APPLY this with VALUES

SELECT Dates, [Time], Item, Food
FROM yourtable
CROSS APPLY (VALUES 
            ('Item1', Item1),
            ('Item2', Item2),
            ('Item3', Item3)) as Items(Item, Food);

Test here

edited Dec 07 '18 at 19:42

answered Dec 07 '18 at 16:22

LukStorms

28,916
5
31
45

Thank you, I did not think about cross apply. It gives me an error saying that there is an incorrect syntax near '('. Do you know what can it be? – zhivab Dec 07 '18 at 16:34
@zhivab It works for me when I try this with sample data. [And it's been in Microsoft SQL Server for a long time](https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/), so I doubt it's because you have a version that won't support it... You sure you didn't add something extra besides changing the tablename to your table? – LukStorms Dec 07 '18 at 16:47
Yes, I am sure. I still have the same error. My SQL Server Version is SQL Server Management Studio v17.7 – zhivab Dec 07 '18 at 16:51
Funny thing, I just solved [another question](https://stackoverflow.com/a/53714815/4003419) about that error you had. Turns out that because he was connecting to a Azure SQL Data Warehouse that using VALUES isn't fully implemented on that. – LukStorms Dec 10 '18 at 23:40

How can I unpivot multiple columns into name and value?

2 Answers2