有一个表CREATE TABLE [dbo].[RawData](
[Region] [varchar](10) NULL,
[Category] [char](1) NULL,
[Amount] [int] NULL,
[SalesDate] [datetime] NULL,
[scor] [nvarchar](50) NULL
) ON [PRIMARY]GODECLARE @SQLStr NVARCHAR(1024)
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as aSET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM RawData PIVOT (Sum (Amount) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStrEXEC sp_executesql @SQLStr
以 category为轴,region 为透视列,做了一个旋转,当且仅当 除了 Amount 外其它4列值相同的时候组成一行,否则另起一行
结果如下:X 24 NULL 11 NULL
X NULL NULL NULL 36
X NULL NULL 86 NULL
X NULL NULL 68 NULL
Y NULL NULL 24 NULL
Y NULL NULL 12 NULL
Y NULL 28 15 NULL
Y NULL NULL 47 NULL
Y 38 62 NULL NULL
Y NULL NULL 117 NULL
Y NULL 91 72 NULL
Z 83 NULL 33 NULL
Z NULL 55 NULL 44
明显看到,因为受其它列值的影响, X,Y,Z形成了多行。
我现在希望不考虑其它值,只要category 相同 就 把值用SUM 合并。
如果我又要求,只要category ,SalesDate 相同 也用SUM 合并,如果scor 不相等。即使前2列值相同,也重新形成一个新行。
一句话。PIVOT 做旋转时,默认的考虑所有列(除参与聚合的列)的值是否相等,不等就形成了一个新行,我现在是否能自己指定哪些列有效,哪些列可以不考虑值是否相等。
[Region] [varchar](10) NULL,
[Category] [char](1) NULL,
[Amount] [int] NULL,
[SalesDate] [datetime] NULL,
[scor] [nvarchar](50) NULL
) ON [PRIMARY]GODECLARE @SQLStr NVARCHAR(1024)
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as aSET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM RawData PIVOT (Sum (Amount) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStrEXEC sp_executesql @SQLStr
以 category为轴,region 为透视列,做了一个旋转,当且仅当 除了 Amount 外其它4列值相同的时候组成一行,否则另起一行
结果如下:X 24 NULL 11 NULL
X NULL NULL NULL 36
X NULL NULL 86 NULL
X NULL NULL 68 NULL
Y NULL NULL 24 NULL
Y NULL NULL 12 NULL
Y NULL 28 15 NULL
Y NULL NULL 47 NULL
Y 38 62 NULL NULL
Y NULL NULL 117 NULL
Y NULL 91 72 NULL
Z 83 NULL 33 NULL
Z NULL 55 NULL 44
明显看到,因为受其它列值的影响, X,Y,Z形成了多行。
我现在希望不考虑其它值,只要category 相同 就 把值用SUM 合并。
如果我又要求,只要category ,SalesDate 相同 也用SUM 合并,如果scor 不相等。即使前2列值相同,也重新形成一个新行。
一句话。PIVOT 做旋转时,默认的考虑所有列(除参与聚合的列)的值是否相等,不等就形成了一个新行,我现在是否能自己指定哪些列有效,哪些列可以不考虑值是否相等。
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as aSET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM RawData PIVOT (Sum (Amount) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStrEXEC sp_executesql @SQLStr
结果:
Category MidWest NorthEast South West
X 24 NULL 11 NULL
X NULL NULL NULL 36
Y NULL NULL 12 NULL
Y NULL 28 15 NULL
Z 83 NULL 33 NULL
Z NULL 55 NULL 44我要的结果
Category MidWest NorthEast South West
X 24 NULL 11 36
Y NULL 28 27 NULL
Z 83 55 33 44
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as aSET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM RawData PIVOT (Sum (isnull(Amount,0)) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStrEXEC sp_executesql @SQLStr
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as aSET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM (select Category,Region,Amount from RawData)as a --這里加入需要顯示的列
PIVOT (Sum (Amount) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStrEXEC sp_executesql @SQLStr
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as aSET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM (select Category,Region,Amount from RawData)as a
PIVOT (Sum (Amount) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStrEXEC sp_executesql @SQLStr
(select Category,Region,Amount from RawData)as a --這里加入需要顯示的列