我使用的是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相关。
我如何才能做到这一点?
发布于 2013-09-04 23:03:18
取消透视数据的一种更简单的方法是使用交叉应用来成对取消透视列:
select vendorid, orders, orders1
from pvt1
cross apply
(
select emp1, sa union all
select emp2, sa1
) c (orders, orders1);
参见SQL Fiddle with Demo。或者,如果不想使用UNION ALL,可以将CROSS APPLY与VALUES子句一起使用:
select vendorid, orders, orders1
from pvt1
cross apply
(
values
(emp1, sa),
(emp2, sa1)
) c (orders, orders1);
发布于 2020-10-14 01:31:14
Taryn的答案确实非常有用,我想扩展它的一个方面。
如果您有一个非常不规范的表,其中包含多组列,例如4个季度或12个月:
+-------+------+------+------+------+------+------+-------+------+
| cYear | foo1 | foo2 | foo3 | foo4 | bar1 | bar2 | bar3 | bar4 |
+-------+------+------+------+------+------+------+-------+------+
| 2020 | 42 | 888 | 0 | 33 | one | two | three | four |
+-------+------+------+------+------+------+------+-------+------+
然后,当您掌握了交叉应用方法的诀窍时,它就很容易编写和理解。对于编号列,请使用常量值。
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 |
+-------+----------+-----+-------+
发布于 2021-03-26 02:02:57
我需要合成键和跳过额外的行,以防数据丢失(NULL)。对于ex。当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_3_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行受影响)
https://stackoverflow.com/questions/18617243
复制相似问题