SQL Unpivot 多列数据 [英] SQL Unpivot multiple columns Data
问题描述
我正在使用 SQL Server 2008 并且我正在尝试对数据进行逆透视.这是我正在使用的 SQL 代码,
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
这是上面代码的结果.
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
以上代码的关系是2与4相关,3与9相关.
The relationship from the above code is 2 is related to 4, and 3 is related to 9.
我怎样才能做到这一点?
How can I achieve this?
推荐答案
反透视数据的一种更简单的方法是使用 CROSS APPLY 对成对的列进行反透视:
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);
参见SQL Fiddle with Demo.或者,如果您不想使用 UNION ALL,您可以将 CROSS APPLY 与 VALUES 子句一起使用:
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);
这篇关于SQL Unpivot 多列数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!