SQL Unpivot несколько столбцов данных

Я использую SQL Server 2008 и пытаюсь развернуть данные. Вот код SQL, который я использую,

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

И вот результат приведенного выше кода.

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

Но я хочу, чтобы мой вывод был таким, как указано ниже

VendorID    Orders  Orders1
1           4       2
1           9       3

Отношение из приведенного выше кода: 2 связано с 4, а 3 связано с 9.

Как я могу этого добиться?


sql sql-server sql-server-2008 unpivot
person user1005310    schedule 04.09.2013    source источник
comment
FWIW .. мне помогла следующая ссылка ... mangalpardeshi.blogspot.com /2009/04/   -  person Seymour    schedule 28.03.2017

Ответы (3)


arrow_upward
39
arrow_downward

Более простой способ развернуть данные — использовать CROSS APPLY, чтобы развернуть столбцы попарно:

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

См. SQL Fiddle с демонстрацией. Или вы можете использовать CROSS APPLY с предложением VALUES, если вы не хотите использовать UNION ALL:

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

См. SQL Fiddle с демонстрацией.

person Taryn    schedule 04.09.2013
comment
ничего себе ... это очень полезно, но я никогда не беспокоился об этом. Мне нужно ускориться в CROSS APPLY. - person Mike M; 15.06.2017

arrow_upward
1
arrow_downward

Ответ Тарин действительно очень полезен, и я хотел бы расширить один его аспект.

Если у вас есть очень ненормализованная таблица, подобная этой, с несколькими наборами столбцов, например. 4 квартала или 12 месяцев:

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

Тогда метод CROSS APPLY легко написать и понять, если вы освоите его. Для нумерованного столбца используйте постоянные значения.

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)

Результат:

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

скрипт SQL

person Andre    schedule 13.10.2020

arrow_upward
0
arrow_downward

Мне нужен составной ключ И пропустить дополнительную строку в случае отсутствия данных (NULL). Например при x2 и y2 возможна замена поставщика и цены

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

производит

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

ext                       31          311         33          333
xy_4_5                           41          411         42          422
xy_4_6                           51          511         52          522

(8 rows affected)

из:

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 рядов)

person Kalju Pärn    schedule 25.03.2021
comment
Пожалуйста, представьте свой ответ независимым заявлением. Или, может быть, он предлагает альтернативное решение, но в отличие от чего? Вы имеете в виду другой ответ? Оригинальный вопрос? - person DubiousPusher; 25.03.2021