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 и выиграл Золотую медаль.