简化动态SQL数据透视表 [英] Simplify Dynamic SQL Pivot Table
问题描述
我已基于以下内容编写了动态数据透视表查询.这是 SQL FIDDLE 供参考.
I have written a Dynamic Pivot Table Query based on the following. Here is a SQL FIDDLE for reference.
CREATE TABLE TestTable1 ([idnumber] INT, [DataTypeId] INT)
GO
INSERT INTO TestTable1
VALUES (1, 108), (1, 108), (1, 108), (2, 108),
(2, 108), (3, 108), (1, 109),(1, 109),
(1, 110),(2, 110),(1, 111),(4, 108),(4, 108),
(4, 110),(4, 111)
GO
这是我编写的动态SQL
Here is the Dynamic SQL that I wrote
DECLARE @SQL NVARCHAR(MAX),
@Cols NVARCHAR(MAX),
@ColsP NVARCHAR(MAX)
SELECT @Cols = STUFF((select ',
ISNULL([' + CAST([DataTypeId] as varchar(10)) + '], 0) AS ''' + CAST([DataTypeId] as varchar(10)) + ''''
FROM
(
SELECT [DataTypeId] FROM [TestTable1]
GROUP BY [DataTypeId]
HAVING [DataTypeId] <> ''
) AS d
ORDER BY [DataTypeId] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
-- /////////////THIS IS WHAT I WANT REMOVED ////////////////////
SELECT @ColsP = STUFF((select ',
[' + CAST([DataTypeId] as varchar(10)) + ']'
FROM
(
SELECT [DataTypeId] FROM [TestTable1]
GROUP BY [DataTypeId]
HAVING [DataTypeId] <> ''
) AS d
ORDER BY [DataTypeId] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
-- /////////////////////////////////////////////////////////////
SET @SQL = 'SELECT idnumber,' + @Cols + '
FROM
(SELECT idnumber, COUNT([DataTypeId]) AS Total, [DataTypeId] FROM [TestTable1]
GROUP BY idnumber, [DataTypeId]
HAVING [DataTypeId] <> ''''
) p
PIVOT
(
SUM(Total) FOR [DataTypeId] IN (' + @ColsP + ')
) AS pvt
ORDER BY pvt.idnumber'
-- print @SQL
EXECUTE( @SQL)
我得到了想要的结果:
| IDNUMBER | 108 | 109 | 110 | 111 |
|----------|-----|-----|-----|-----|
| 1 | 3 | 2 | 1 | 1 |
| 2 | 2 | 0 | 1 | 0 |
| 3 | 1 | 0 | 0 | 0 |
| 4 | 2 | 0 | 1 | 1 |
但是我相信它可以做得更好.我想删除填充变量@ColsP
-SELECT @ColsP = STUFF((select...")
But I am sure it can be done better. I would like to remove where I populate the variable @ColsP
- SELECT @ColsP = STUFF((select...")
应该有一种方法,我可以通过TestTable1
进行一次循环来创建此动态代码.如您所见,我循环浏览了两次.一次读取要为select语句创建哪些列,一次读取PIVOT表.
There should be a way where I can create this dynamic code with just one loop through TestTable1
. As you can see, I loop through it twice. Once to read what columns to create for the select statement, and once for the PIVOT table.
以下是动态SQL生成的代码:
Here is the code that is generated by the Dynamic SQL:
SELECT idnumber,
ISNULL([108], 0) AS '108',
ISNULL([109], 0) AS '109',
ISNULL([110], 0) AS '110',
ISNULL([111], 0) AS '111'
FROM
(
SELECT idnumber, COUNT([DataTypeId]) AS Total, [DataTypeId]
FROM [TestTable2]
GROUP BY idnumber, [DataTypeId]
HAVING [DataTypeId] <> ''
) p
PIVOT
(
SUM(Total) FOR [DataTypeId] IN ([108], [109], [110], [111])
) AS pvt
ORDER BY pvt.idnumber
推荐答案
您可以大大缩短代码.首先,您可以使用count
聚合PIVOT中的数据.不需要内部计数来聚合数据或HAVING
子句.最后,您只需要创建一次列列表.您可以轻松地将代码改进为:
You can shorten your code considerably. First, you can just use count
to aggregate the data in the PIVOT. There is no need for the inner count to aggregate the data or the HAVING
clause. Finally, you only need to create the list of columns once. You could easily improve the code to:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(DataTypeId)
from TestTable1
group by DataTypeId
order by DataTypeId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= N'SELECT idnumber, ' + @cols + N'
from
(
select idnumber, DataTypeId
from TestTable1
) x
pivot
(
count(DataTypeId)
for DataTypeId in (' + @cols + N')
) p '
exec sp_executesql @query;
请参见带演示的SQL提琴.这样会得到相同的结果:
See SQL Fiddle with Demo. This gives the same result:
| IDNUMBER | 108 | 109 | 110 | 111 |
|----------|-----|-----|-----|-----|
| 1 | 3 | 2 | 1 | 1 |
| 2 | 2 | 0 | 1 | 0 |
| 3 | 1 | 0 | 0 | 0 |
| 4 | 2 | 0 | 1 | 1 |
这篇关于简化动态SQL数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!