name qty date
a 1742 2003-02
a 168 2003-01
a 3297 2002-12
a 552 2002-11
b 119 2003-02
b 128 2003-01
b 17 2002-12
得出结果:
name 2003-02 2003-01 2002-12 2002-11
a 1742 168 3297 552
b 119 128 17 0
...全文
467打赏收藏
求SQL写法!急
table test name qty date a 1742 2003-02 a 1682003-01 a 32972002-12 a 5522002-11 b 1192003-02 b 1282003-01 b 172002-12 得出结果: name 2003-02 2003-01 2002-12 2002-11 a 1742 168 3297 552 b 119 128 17 0
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
下面是用于创建旋转结果的 SELECT 语句:
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
该 SELECT 语句还处理其中每个季度占多行的表。GROUP BY 语句将 Pivot 中一年的所有行合并成一行输出。当执行分组操作时,SUM 聚合中的 CASE 函数的应用方式是这样的:将每季度的 Amount 值添加到结果集的适当列中,在其它季度的结果集列中添加 0。
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO
带有 CUBE 的 GROUP BY 和带有 ROLLUP 的 GROUP BY 都计算与本例显示相同的信息种类,但格式稍有不同。
declare @sql varchar(8000)
set @sql =''
select @sql = sql +',sum(case when date='''+ date + ''' then qty else 0 end ) as ' + date from (select distinct date from table) aaa
exec ('select name '+@sql + ' from table group by name ')