sql行转列实例
目的：利用Northwind数据库的Orders,Order Details,Products表实现每一种产品按年月统计销售量，按产品名称和年月做标题显示。如下图所示:
1
--按年月统计
2DECLARE @StartYear int
3DECLARE @Year nvarchar(4)
4DECLARE @EndYear int
5DECLARE @Month int
6DECLARE @M nvarchar(2)
7DECLARE @SQl nvarchar(MAX)
8SET @SQL='SELECT OD.ProductID,ProductName'
9
10SELECT @StartYear=DATENAME(YEAR,MIN(OrderDate)) FROM [Orders]
11SELECT @EndYear=DATENAME(YEAR,MAX(OrderDate)) FROM [Orders]
12
13WHIlE(@StartYear<=@EndYear)
14 BEGIN
15 SET @Year=CAST(@StartYear AS varchar(4))
16 SET @Month=1
17 WHILE(@Month<13)
18 BEGIN
19 SET @M=right('0'+CAST(@Month as NVARCHAR(2)),2)
20 SET @SQL=@SQL+',SUM(CASE WHEN (DATENAME(YEAR,OrderDate)='''+@Year+''' AND DATENAME(Month,OrderDate)='''+@M+''') THEN Quantity ELSE 0 END) as '''+@Year+'-'+@M+''''
21 SET @Month=@Month+1
22 END
23
24 SET @StartYear=@StartYear+1
25 END
26
27SET @SQL=@SQL+' FROM Orders O LEFT OUTER JOIN [Order Details] OD ON O.OrderID=OD.OrderID'+
28 ' LEFT JOIN [Products] P on OD.ProductID=P.ProductID '+
29' GROUP BY OD.ProductID,ProductName ORDER BY OD.ProductID'
30exec(@SQL)
31print(@SQL)
--按年月统计2
DECLARE @StartYear int3
DECLARE @Year nvarchar(4)4
DECLARE @EndYear int5
DECLARE @Month int6
DECLARE @M nvarchar(2)7
DECLARE @SQl nvarchar(MAX)8
SET @SQL='SELECT OD.ProductID,ProductName'9
10
SELECT @StartYear=DATENAME(YEAR,MIN(OrderDate)) FROM [Orders]11
SELECT @EndYear=DATENAME(YEAR,MAX(OrderDate)) FROM [Orders]12
13
WHIlE(@StartYear<=@EndYear)14
BEGIN15
SET @Year=CAST(@StartYear AS varchar(4))16
SET @Month=117
WHILE(@Month<13)18
BEGIN19
SET @M=right('0'+CAST(@Month as NVARCHAR(2)),2)20
SET @SQL=@SQL+',SUM(CASE WHEN (DATENAME(YEAR,OrderDate)='''+@Year+''' AND DATENAME(Month,OrderDate)='''+@M+''') THEN Quantity ELSE 0 END) as '''+@Year+'-'+@M+''''21
SET @Month=@Month+122
END23
24
SET @StartYear=@StartYear+125
END26
27
SET @SQL=@SQL+' FROM Orders O LEFT OUTER JOIN [Order Details] OD ON O.OrderID=OD.OrderID'+28
' LEFT JOIN [Products] P on OD.ProductID=P.ProductID '+29
' GROUP BY OD.ProductID,ProductName ORDER BY OD.ProductID'30
exec(@SQL)31
print(@SQL)
其实，这里最主要的代码是组合CASE语句，还可以考虑把第一个年份的前面月份和最后年份的后面月份去掉。随便也贴下代码：
1
2
3--按年月统计
4DECLARE @StartYear int
5DECLARE @Year nvarchar(4)
6DECLARE @EndYear int
7DECLARE @TmpYear int
8DECLARE @Month int,@Month1 int,@Month2 int
9DECLARE @MaxMonth int
10DECLARE @M nvarchar(2)
11DECLARE @SQl nvarchar(MAX)
12SET @SQL='SELECT OD.ProductID,ProductName'
13
14SELECT @StartYear=DATENAME(YEAR,MIN(OrderDate)),@Month1=DATENAME(Month,MIN(OrderDate)) FROM [Orders]
15SELECT @EndYear=DATENAME(YEAR,MAX(OrderDate)),@Month2=DATENAME(Month,MAX(OrderDate)) FROM [Orders]
16
17SET @TmpYear=@StartYear
18
19WHIlE(@TmpYear<=@EndYear)
20 BEGIN
21 SET @Year=CAST(@TmpYear AS varchar(4))
22 SET @Month=1
23 SET @MaxMonth=13
24 IF(@TmpYear=@StartYear)
25 SET @Month=@Month1
26 ELSE IF(@TmpYear=@EndYear)
27 SET @MaxMonth=@Month2+1
28 WHILE(@Month<@MaxMonth)
29 BEGIN
30 SET @M=right('0'+CAST(@Month as NVARCHAR(2)),2)
31 SET @SQL=@SQL+',SUM(CASE WHEN (DATENAME(YEAR,OrderDate)='''+@Year+''' AND DATENAME(Month,OrderDate)='''+@M+''') THEN Quantity ELSE 0 END) as '''+@Year+'-'+@M+''''
32 SET @Month=@Month+1
33 END
34
35 SET @TmpYear=@TmpYear+1
36 END
37
38SET @SQL=@SQL+' FROM Orders O LEFT OUTER JOIN [Order Details] OD ON O.OrderID=OD.OrderID'+
39 ' LEFT JOIN [Products] P on OD.ProductID=P.ProductID '+
40' GROUP BY OD.ProductID,ProductName ORDER BY OD.ProductID'
41exec(@SQL)
42print(@SQL)
43
44
45
46
2
3
--按年月统计4
DECLARE @StartYear int5
DECLARE @Year nvarchar(4)6
DECLARE @EndYear int7
DECLARE @TmpYear int8
DECLARE @Month int,@Month1 int,@Month2 int9
DECLARE @MaxMonth int10
DECLARE @M nvarchar(2)11
DECLARE @SQl nvarchar(MAX)12
SET @SQL='SELECT OD.ProductID,ProductName'13
14
SELECT @StartYear=DATENAME(YEAR,MIN(OrderDate)),@Month1=DATENAME(Month,MIN(OrderDate)) FROM [Orders]15
SELECT @EndYear=DATENAME(YEAR,MAX(OrderDate)),@Month2=DATENAME(Month,MAX(OrderDate)) FROM [Orders]16
17
SET @TmpYear=@StartYear18
19
WHIlE(@TmpYear<=@EndYear)20
BEGIN21
SET @Year=CAST(@TmpYear AS varchar(4))22
SET @Month=123
SET @MaxMonth=1324
IF(@TmpYear=@StartYear)25
SET @Month=@Month126
ELSE IF(@TmpYear=@EndYear)27
SET @MaxMonth=@Month2+128
WHILE(@Month<@MaxMonth)29
BEGIN30
SET @M=right('0'+CAST(@Month as NVARCHAR(2)),2)31
SET @SQL=@SQL+',SUM(CASE WHEN (DATENAME(YEAR,OrderDate)='''+@Year+''' AND DATENAME(Month,OrderDate)='''+@M+''') THEN Quantity ELSE 0 END) as '''+@Year+'-'+@M+''''32
SET @Month=@Month+133
END34
35
SET @TmpYear=@TmpYear+136
END37
38
SET @SQL=@SQL+' FROM Orders O LEFT OUTER JOIN [Order Details] OD ON O.OrderID=OD.OrderID'+39
' LEFT JOIN [Products] P on OD.ProductID=P.ProductID '+40
' GROUP BY OD.ProductID,ProductName ORDER BY OD.ProductID'41
exec(@SQL)42
print(@SQL)43
44
45
46
