Упрощение динамической сводной таблицы SQL

Я написал запрос динамической сводной таблицы на основе следующего. Вот SQL FIDDLE для справки.

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, который я написал

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...")

Должен быть способ создать этот динамический код всего одним циклом через TestTable1. Как видите, я прокручиваю его дважды. Один раз, чтобы прочитать, какие столбцы создать для оператора select, и один раз для таблицы PIVOT.

Вот код, сгенерированный динамическим 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

sql-server tsql dynamic-sql pivot
person Chuck    schedule 15.10.2014    source источник
comment
Не уверен, что вы спрашиваете. Я не определяю SP или функцию здесь. Просто запрос sql в это время. Тамкс   -  person Chuck    schedule 15.10.2014

Ответы (2)


arrow_upward
2
arrow_downward

Вы можете значительно сократить свой код. Во-первых, вы можете просто использовать count для агрегирования данных в PIVOT. Нет необходимости во внутреннем счетчике для агрегирования данных или предложения HAVING. Наконец, вам нужно создать список столбцов только один раз. Вы можете легко улучшить код:

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 Fiddle с демонстрацией. Это дает тот же результат:

| 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 |
person Taryn    schedule 15.10.2014
comment
Большое тебе спасибо. Это именно то, что я искал. Еще лучше... Единственное дополнение, сделанное в этом коде, это: В первом операторе SELECT я добавил: HAVING DataTypeId ‹› '' в моем реальном наборе данных есть поля с пустыми строками, создав дополнительный столбец "пусто" (который конечно нельзя заполнить...), спасибо - person Chuck; 16.10.2014
comment
@ user3204669 Вместо этого вы можете использовать WHERE DataTypeID <> ''. HAVING будет использоваться для фильтрации агрегации. - person Taryn; 16.10.2014

arrow_upward
-1
arrow_downward

Попробуйте заменить на это.

SET NOCOUNT ON
IF OBJECT_ID('TestTable1') IS NOT NULL
DROP TABLE TestTable1
GO

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)

DECLARE
    @AllColumns NVARCHAR(MAX)

SELECT @AllColumns = ''

SELECT @AllColumns = @AllColumns +
  '[' + CAST(DataTypeId as NVARCHAR)+'],' 
FROM TestTable1
GROUP BY DataTypeId


SET @AllColumns = LEFT(@AllColumns,LEN(@AllColumns)-1)
PRINT @AllColumns
person SubqueryCrunch    schedule 15.10.2014
comment
Может быть, я что-то упустил из вашего поста. Видя, как я динамически создаю столбцы для нового запроса, столбцы таблицы («TestTable1») на самом деле не используются. Это «idnumber» и «DataTypeId». Что мне нужно, так это способ без запроса, без цикла получить то, что заполняется в @ColsP (а именно: [108], [109], [110], [111]). Спасибо. - person Chuck; 15.10.2014
comment
Приношу свои извинения, хотя техника такая же, это правильный способ сделать это (я отредактировал свой ответ). - person SubqueryCrunch; 16.10.2014
comment
Здравствуйте, и спасибо. Это действительно хороший код. Это действительно возвращает имена столбцов таблицы. Я посмотрю, смогу ли я включить это. Спасибо. - person Chuck; 18.10.2014