SQL Unpivot multiple columns Data

Question

I am using SQL server 2008 and I am trying to unpivot the data. Here is the SQL code that I am using,

CREATE TABLE #pvt1 (VendorID int, Sa int, Emp1 int,Sa1 int,Emp2 int)
GO
INSERT INTO #pvt1  VALUES (1,2,4,3,9);

GO

--Unpivot the table.
SELECT distinct VendorID,Orders,Orders1
FROM 
   (SELECT VendorID, Emp1, Sa,Emp2,Sa1
   FROM #pvt1 ) p
UNPIVOT
   (Orders FOR Emp IN 
      (Emp1,Emp2)
)AS unpvt
UNPIVOT
   (Orders1 FOR Emp1 IN 
      (Sa,Sa1)
)AS unpvt1;
GO

And Here is the result of the above code.

VendorID    Orders  Orders1
1            4      2
1            4      3
1            9      2
1            9      3

But I want my Output to be the way indicated below

VendorID    Orders  Orders1
1           4       2
1           9       3

The relationship from the above code is 2 is related to 4, and 3 is related to 9.

How can I achieve this?

FWIW .. the following link helped me ... http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.html — Seymour, Mar 28 '17 at 14:06

score 48 · Accepted Answer · answered Sep 04 '13 at 15:03

48

An easier way to unpivot the data would be to use a CROSS APPLY to unpivot the columns in pairs:

select vendorid, orders, orders1
from pvt1
cross apply
(
  select emp1, sa union all
  select emp2, sa1
) c (orders, orders1);

See SQL Fiddle with Demo. Or you can use CROSS APPLY with the VALUES clause if you don't want to use the UNION ALL:

select vendorid, orders, orders1
from pvt1
cross apply
(
  values 
    (emp1, sa),
    (emp2, sa1)
) c (orders, orders1);

See SQL Fiddle with Demo

answered Sep 04 '13 at 15:03

Taryn

242,637
56
362
405

2

wow... this is super useful yet I've never bothered with it. I need to get up to speed on CROSS APPLY. – Mike M Jun 15 '17 at 00:53
1

This is a glorious alternative to unpivot code. I love CROSS APPLY! – eddyizm Oct 01 '21 at 15:58

score 4 · Answer 2 · answered Oct 13 '20 at 17:31

The answer by Taryn is indeed super useful, and I'd like to expand one aspect of it.

If you have a very un-normalized table like this, with multiple sets of columns for e.g. 4 quarters or 12 months:

+-------+------+------+------+------+------+------+-------+------+
| cYear | foo1 | foo2 | foo3 | foo4 | bar1 | bar2 | bar3  | bar4 |
+-------+------+------+------+------+------+------+-------+------+
|  2020 |   42 |  888 |    0 |   33 | one  | two  | three | four |
+-------+------+------+------+------+------+------+-------+------+

Then the CROSS APPLY method is easy to write and understand, when you got the hang of it. For the numbered column, use constant values.

SELECT 
    cYear,
    cQuarter,
    foo,
    bar
FROM temp

CROSS APPLY
(
  VALUES
    (1, foo1, bar1),
    (2, foo2, bar2),
    (3, foo3, bar3),
    (4, foo4, bar4)

) c (cQuarter, foo, bar)

Result:

+-------+----------+-----+-------+
| cYear | cQuarter | foo |  bar  |
+-------+----------+-----+-------+
|  2020 |        1 |  42 | one   |
|  2020 |        2 | 888 | two   |
|  2020 |        3 |   0 | three |
|  2020 |        4 |  33 | four  |
+-------+----------+-----+-------+

SQL Fiddle

Kalju Pärn · Answer 3 · 2021-03-27T06:30:11.217

I needed composit key AND skip extras row in case when data is missing (NULLs). For ex. when x2 and y2 are possible replacement vendor and price

WITH pvt AS (SELECT * FROM (VALUES 
   ( 1, 6, 11, 111, 12, 13, 122, 133),
   ( 2, 6, 21, 211, 22, 23, 222, 233),
   ( 3, 6, 31, 311, 32, 33, 322, 333),
   ( 5, 4, 41, 411, 42, NULL, 422, NULL),
   ( 6, 4, 51, 511, 52, NULL, 522, NULL))
   s( id, s, a, b, x1, x2, y1, y2)
)
-- SELECT * FROM pvt

SELECT CONCAT('xy_',s,'_', id, postfix) as comp_id, a, b, x, y 
FROM pvt
CROSS APPLY
(
  VALUES
    (NULL, x1, y1),   
    ('_ext', x2, y2)

) c (postfix, x, y)
WHERE x IS NOT NULL

produces

comp_id                          a           b           x           y
-------------------------------- ----------- ----------- ----------- -----------
xy_6_1                           11          111         12          122
xy_6_1_ext                       11          111         13          133
xy_6_2                           21          211         22          222
xy_6_2_ext                       21          211         23          233
xy_6_3                           31          311         32          322
xy_6_3_ext                       31          311         33          333
xy_4_5                           41          411         42          422
xy_4_6                           51          511         52          522

(8 rows affected)

from:

id          s           a           b           x1          x2          y1          y2
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           6           11          111         12          13          122         133
2           6           21          211         22          23          222         233
3           6           31          311         32          33          322         333
5           4           41          411         42          NULL        422         NULL
6           4           51          511         52          NULL        522         NULL

(5 rows affected)

Please introduce your answer with an independent statement "Or may be" is proposing an alternative solution but as opposed to what? Are you referring to another answer? The original question? — DubiousPusher, Mar 25 '21 at 18:42

SQL Unpivot multiple columns Data

3 Answers3

Linked

Related