Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql server - Simplify Dynamic SQL Pivot Table

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

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)

I get the result that I want:

| 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 |

But I am sure it can be done better. I would like to remove where I populate the variable @ColsP - SELECT @ColsP = STUFF((select...")

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.

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

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 |

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...