CODEWITHSUNDEEP
sqlsql-server
Patrick Bender
Patrick Bender

Reputation: 467

Unpivot SQL Table with multiple columns

I would like to unpivot a SQL table around multiple columns. I have tried a normal UNPIVOT statement but that only ppivots around one value. See this link for example: https://codingsight.com/understanding-pivot-unpivot-and-reverse-pivot-statements.

I have tried to illustrate my data as well as my desired outcome in the picture below. The top table is a sample of the data in the SQL table. I have used 3 materials but in reality there are 20.

The bottom table is my desired outcome.

The data is on a SQL 2008-r2 server.

Any pointers on how to go about this task?

enter image description here

Upvotes: 1

Views: 225

Answers (2)

GMB
GMB

Reputation: 222492

Consider using cross apply, like so:

select t.date, t.product, x.*
from mytable t
cross apply (values 
    (container1material, container1amount),
    (container2material, container2amount),
    (container3material, container3amount)
) x(material, amount)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use apply for unpivoting:

select t.date, t.product, v.*
from t cross apply
     (values (container1amount, container1material),
             (container2amount, container2material),
             (container3amount, container3material)
     ) v(containeramount, containermaterial);

unpivot is bespoke syntax (not-standard) and it only does one thing. By contrast, lateral joins are very powerful and unpivoting is only one thing that you can do with them. Apply is worth learning about.

Upvotes: 1

Related Questions