SQL Dynamic Pivot - как упорядочить столбцы

Question

Я работаю над динамическим сводным запросом к таблице, которая содержит:

• OID - OrderID
• Размер - размер продукта
• BucketNum - порядок того, что размеры должны идти
• количество - сколько заказано

Столбец размера содержит разные размеры в зависимости от OID.

Итак, используя код, найденный здесь, я собрал это:

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)

SELECT  @listCol = STUFF(( SELECT distinct  '], [' + [size]
                           FROM     #t
                         FOR
                           XML PATH('')
                         ), 1, 2, '') + ']'


SET @query = 'SELECT * FROM
      (SELECT OID,  [size], [quantity]
            FROM #t 
            ) src
PIVOT (SUM(quantity) FOR Size
IN (' + @listCol + ')) AS pvt'


EXECUTE ( @query )

Это прекрасно работает, за исключением того, что заголовки столбцов (метки размеров) расположены не в том порядке, который основан на столбце bucketnum. В порядке, основанном на размерах.

Я попробовал дополнительный Order By после разворота, но это не работает.

Как мне контролировать порядок появления столбцов?

Спасибо

Answer 1

Вам нужно исправить это:

SELECT  @listCol = STUFF(( SELECT distinct  '], [' + [size]
                           FROM     #t
                         FOR
                           XML PATH('')
                         ), 1, 2, '') + ']'

Чтобы вернуть столбцы в правильном порядке. Возможно, вам придется сделать что-то подобное, а не использовать DISTINCT:

SELECT [size]
FROM     #t
GROUP BY [size]
ORDER BY MIN(BucketNum)

Answer 2

SELECT @listCol = STUFF(
        (SELECT DISTINCT ',' + QUOTENAME(size) AS [size]
        FROM #t
        ORDER BY [size]
        FOR XML PATH('')

Answer 3

У меня была та же проблема, и я попробовал решение, предложенное выше, но, вероятно, из-за моего уровня понимания, не смог заставить его работать. Я обнаружил, что простым взломом было создать таблицу Temp с правильно упорядоченными заголовками столбцов, используя операторы Order by, а затем включить в этот список переменную, которая задает имена столбцов динамического сводного запроса.

например

SELECT WeekNum INTO #T3 
FROM #T2 
GROUP BY WeekNum 
ORDER BY MIN(WeekNum) 

SELECT @ColumnName1 = ISNULL(@ColumnName1 + ',','') + QuoteName(WeekNum) 
FROM (SELECT WeekNum From #T3) AS WeekNum

Работал лакомство

Надеюсь, что это помогает кому-то.

Answer 4

Я видел эту ссылку только сегодня, которая использует CTE для построения списка столбцов (который, по-видимому, вы могли бы заказать) на лету без необходимости динамического sql:

http://blog.stevienova.com/2009/07/13/using-ctes-to-create-dynamic-pivot-tables-in-sql-20052008/