T-SQL: Динамическое транспонирование нескольких колонок

T-SQL: Динамическое транспонирование нескольких колонок

Эта статья является авторским переводом моей статьи T-SQL: Dynamic Pivot on Multiple Columns

Проблема транспонирования строк в колонки является одной из наиболее часто обсуждаемых проблем в MSDN Transact-SQL forum. Нередко проблема усложняется необходимостью выполнять транспонирование динамически (когда набор значений постоянно меняется). Спрашивающие часто забывают, что динамически транспонировать данные легче на клиентском приложении, например, в SSRS такая возможность включена. Если же мы хотим сделать такой запрос на сервере, нам придется использовать динамический код запроса, а это часто нетривиальная задача. Однако, если мы все же решили создать подобный запрос на сервере, надо помнить, что динамический запрос можно создать, для начала хорошо представив, как будет выглядеть этот же запрос статически. Если это понять и прочувствовать, написание динамических запросов будет достаточно простой задачей.

У меня уже есть блог на английском на эту же тему:  
Dynamic PIVOT on multiple columns   .

Чтобы не пересказывать содержание этого блога, я лучше рассмотрю другой пример из недавнего вопроса на форуме. 


 Для решения проблемы, заданной в вопросе по ссылке, я написала следующее решение для неизвестного заранее числа колонок:


USE tempdb
 
CREATE TABLE tblTest (
    Id INT
    ,Col_1 INT
    )
 
INSERT INTO tblTest
VALUES (
    1
    ,12345
    )
    ,(
    1
    ,23456
    )
    ,(
    1
    ,45678
    )
    ,(
    2
    ,57823
    )
    ,(
    2
    ,11111
    )
    ,(
    2
    ,34304
    )
    ,(
    2
    ,12344
    )
 
DECLARE @MaxCount INT;
 
SELECT @MaxCount = max(cnt)
FROM (
    SELECT Id
        ,count(Col_1) AS cnt
    FROM tblTest
    GROUP BY Id
    ) X;
 
DECLARE @SQL NVARCHAR(max)
    ,@i INT;
 
SET @i = 0;
SET @SQL = '';
 
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SET @SQL = @Sql + ',
    MAX(CASE WHEN RowNo = ' cast(@i AS NVARCHAR(10)) + ' THEN  Col_1 END) AS Col' cast(@i AS NVARCHAR(10));
END
 
SET @SQL = N';WITH CTE AS (
   SELECT ID, Col_1, row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS rowno
   FROM   tblTest
)
SELECT ID ' + @SQL + N'
FROM   CTE
GROUP  BY ID';
 
PRINT @SQL;
 
EXECUTE (@SQL);


В этом решении первым важным шагом было понять, что нужно использовать ROW_NUMBER() функцию. Я использовала CASE выражение, так как это удобнее, когда нам надо транспонировать несколько колонок сразу (хотя в данном примере мы транспонировали по одной колонке). На этом примере мы видим, что очень важно понять, как запрос будет выглядеть статически. Поняв это, мы легко переводим его в динамический, используя конструкцию WHILE LOOP.

Для полноты картины я также приведу решение с использованием PIVOT выражения:

DECLARE @MaxCount INT;
 
SELECT @MaxCount = max(cnt)
FROM (
    SELECT Id
        ,count(Col_1) AS cnt
    FROM tblTest
    GROUP BY Id
    ) X;
 
DECLARE @SQL NVARCHAR(max)
    ,@i INT;
 
SET @i = 0;
 
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SET @SQL = COALESCE(@Sql + ', ''') + 'Col' cast(@i AS NVARCHAR(10));
END
 
SET @SQL = N';WITH CTE AS (
   SELECT ID, Col_1, ''Col'' + CAST(row_number() OVER (PARTITION BY ID ORDER BY Col_1) AS Varchar(10)) AS RowNo
   FROM   tblTest
)
SELECT *
FROM   CTE
PIVOT (MAX(Col_1) FOR RowNo IN (' + @SQL + N')) pvt';
 
PRINT @SQL;
 
EXECUTE (@SQL);


Как мы видим, оба кода очень похожи. 

Я надеюсь добавить больше примеров в эту статью, как только появятся свежие проблемы на форуме. 

 Недавно был задан похожий вопрос на форуме, и решение из этой статьи пригодилось. Мне даже не пришлось приводить полное решение, достаточным оказалось дать ссылку на эту статью.


Английский оригинал этой статьи участвовал в соревновании 
Technology Guru TechNet WiKi for May и выиграл Золотую медаль. 


Смотрите также


Сортировать по: Дата публикации | Последние | Самый полезный
Комментарии
  • Naomi  N внес(ла) изменения в Revision 17. Комментарий: Added link to a recent thread

Страница 1 из 1 (элементов: 1)