枢轴两柱对于SQL
问题描述:
,我有以下数据:枢轴两柱对于SQL
---------------------------------------------------
oGroup oDate Value1 Value2
---------------------------------------------------
A 2014-01-01 10 100
A 2014-01-02 12 108
B 2014-01-01 20 120
B 2014-01-02 22 140
C 2014-01-01 5 160
C 2014-01-02 9 175
我有枢轴以下脚本:
select
*
from
(
select
Group
, oDate
, Value1
, Value2
from #ret
) src
pivot
(
sum(value1)
for group in ([X1], [X2], [X3])
) piv
order by oDate;
那么结果应该是这样的:
------------------------------------------------
oDate X1 X2 X3
------------------------------------------------
2014-01-01 10 20 5
2014-01-02 12 22 9
我的问题是我怎样才能将value2
添加到X1,X2,X3之后的另外3列。比方说,Y1 Y2和Y3。
所以结果应该是:
------------------------------------------------------
oDate X1 X2 X3 Y1 Y2 Y3
------------------------------------------------------
2014-01-01 10 20 5 100 120 160
2014-01-02 12 22 9 108 140 175
------------------------------------------------------
我怎样才能做到这一点?谢谢。
答
PIVOT
随着:
DECLARE @t TABLE
(
oGroup CHAR(1) ,
oDate DATE ,
Value1 INT ,
Value2 INT
)
INSERT INTO @t
VALUES ('A', '20140101', 10, 100),
('A', '20140102', 12, 108),
('B', '20140101', 20, 120),
('B', '20140102', 22, 140),
('C', '20140101', 5, 160),
('C', '20140102', 9, 175);
WITH cte1
AS (SELECT oGroup ,
oDate ,
Value1
FROM @t
),
cte2
AS (SELECT oGroup ,
oDate ,
Value2
FROM @t
)
SELECT a1.* ,
a2.[A] ,
a2.[B] ,
a2.[C]
FROM (SELECT *
FROM cte1 PIVOT(SUM(Value1) FOR oGroup IN ([A], [B],
[C])) AS p
) a1
JOIN (SELECT *
FROM cte2 PIVOT(SUM(Value2) FOR oGroup IN ([A],
[B], [C])) AS p
) a2 ON a1.oDate = a2.oDate
输出:
oDate A B C A B C
2014-01-01 10 20 5 100 120 160
2014-01-02 12 22 9 108 140 175
答
select
*
into #tb1
from
(
select
Group
, oDate
, Value1
from #ret
) src
pivot
(
sum(value1)
for group in ([X1], [X2], [X3])
) piv
order by oDate;
select
*
into #tb2
from
(
select
Group
, oDate
, Value2
from #ret
) src
pivot
(
sum(value2)
for group in ([Y1], [Y2], [Y3])
) piv
order by oDate;
select * from #tb1 inner join #tb2 on #tb1.oDate = #tb2.oDate