Итого по строке и столбцу в динамическом стержне

Question

В SQL Server 2008 у меня есть таблица (tblStock) с 3 столбцами:

• PartCode (NVARCHAR (50))
• StockQty (INT)
• Местоположение (NVARCHAR (50))

некоторые примеры данных ниже:

    PartCode    StockQty    Location
   .........   .........    .........
    A              10        WHs-A
    B              22        WHs-A
    A               1        WHs-B
    C              20        WHs-A
    D              39        WHs-F
    E               3        WHs-D
    F               7        WHs-A
    A               9        WHs-C
    D               2        WHs-A
    F              54        WHs-E

Как создать процедуру для получения результата, как показано ниже?

PartCode    WHs-A   WHs-B   WHs-C   WHs-D   WHs-E   WHs-F   Total
........    .....   .....   .....  ......   .....   .....   .....
A            10       1       9       0       0       0      20
B            22       0       0       0       0       0      22
C            20       0       0       0       0       0      20
D             2       0       0       0       0      39      41
E             0       0       0       3       0       0       3
F             7       0       0       0      54       0      61
Total        61       1       9       3      54      39     167

Ваша помощь очень ценится, спасибо.

Answer 1

ОБРАЗЕЦ СТОЛ

SELECT * INTO #tblStock
FROM
(
SELECT 'A' PartCode,  10 StockQty, 'WHs-A' Location
UNION ALL
SELECT 'B',   22,  'WHs-A'
UNION ALL
SELECT 'A',   1,   'WHs-B'
UNION ALL
SELECT 'C',   20,  'WHs-A'
UNION ALL
SELECT 'D',   39,  'WHs-F'
UNION ALL
SELECT 'E',   3,   'WHs-D'
UNION ALL
SELECT 'F',   7,   'WHs-A'
UNION ALL
SELECT 'A',   9,   'WHs-C'
UNION ALL
SELECT 'D',   2,   'WHs-A'
UNION ALL
SELECT 'F',   54,  'WHs-E'
)TAB

Получить столбцы для динамического поворота и заменить NULL с zero

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')
               FROM (SELECT DISTINCT Location FROM #tblStock) PV 
               ORDER BY Location 
-- Since we need Total in last column, we append it at last
SELECT @cols += ',[Total]'


--Varible to replace NULL with zero
DECLARE @NulltoZeroCols NVARCHAR (MAX)

SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']' 
FROM (SELECT DISTINCT Location FROM #tblStock)TAB  
ORDER BY Location FOR XML PATH('')),2,8000) 

SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'

Ты можешь использовать CUBE найти строку и столбец итого и заменить NULL с Total для строк, сгенерированных из CUBE,

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT PartCode,' + @NulltoZeroCols + ' FROM 
             (
                 SELECT 
                 ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode, 
                 SUM(StockQty)StockQty , 
                 ISNULL(Location,''Total'')Location              
                 FROM #tblStock
                 GROUP BY Location,PartCode
                 WITH CUBE
             ) x
             PIVOT 
             (
                 MIN(StockQty)
                 FOR Location IN (' + @cols + ')
            ) p
            ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode' 

EXEC SP_EXECUTESQL @query

• Нажмите здесь, чтобы посмотреть результат

РЕЗУЛЬТАТ

ПРИМЕЧАНИЕ: если вы хотите NULL вместо zero в качестве значений используйте @cols вместо @NulltoZeroCols в динамическом сводном коде

РЕДАКТИРОВАТЬ:

1. Показывать только итоговые строки

• Не используйте код SELECT @cols += ',[Total]' а также SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]',
• использование ROLLUP вместо CUBE,

2. Показать только столбец Всего

• Используйте код SELECT @cols += ',[Total]' а также SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]',
• использование ROLLUP вместо CUBE,
• + Изменить GROUP BY Location,PartCode в GROUP BY PartCode,Location,
• Вместо ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode использовать WHERE PartCode<>''TOTAL'' ORDER BY PartCode,

ОБНОВЛЕНИЕ: чтобы принести PartName для ОП

Я обновляю запрос ниже, чтобы добавить PartName с результатом. поскольку PartName добавит дополнительные результаты с CUBE и чтобы избежать путаницы в AND или же OR условия, лучше объединить сводный результат с DISTINCT значения в вашей исходной таблице.

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.PartCode,T.PartName,' + @NulltoZeroCols + ' FROM 
             (                
                 SELECT 
                 ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode, 
                 SUM(StockQty)StockQty , 
                 ISNULL(Location,''Total'')Location              
                 FROM #tblStock
                 GROUP BY Location,PartCode
                 WITH CUBE                   
             ) x
             PIVOT 
             (
                 MIN(StockQty)
                 FOR Location IN (' + @cols + ')
            ) p
            LEFT JOIN
            (  
                SELECT DISTINCT PartCode,PartName
                FROM #tblStock  
            )T
            ON P.PartCode=T.PartCode
            ORDER BY CASE WHEN (P.PartCode=''Total'') THEN 1 ELSE 0 END,P.PartCode' 

EXEC SP_EXECUTESQL @query

• Нажмите здесь, чтобы посмотреть результат

Answer 2

Вам нужно использовать case based aggregation повернуть данные

Чтобы получить total использование строки union

В случае, если Location значения заранее неизвестны, нужно построить dynamic query

Вы также можете использовать pivot Ключевое слово, чтобы сделать то же самое.

select partCode,
 sum( case when Location='WHs-A' then StockQty
      else 0 end
    ) as 'Whs-A',
 sum( case when Location='WHs-B' then StockQty
      else 0 end
    ) as 'Whs-B',
sum(StockQty) as 'Total'
from tblStock
group by partCode
union all
select 'Total' as 'partCode',
sum( case when Location='WHs-A' then StockQty
    else 0 end ) as 'Whs-A',
sum( case when Location='WHs-B' then StockQty
    else 0 end) as 'Whs-B',
sum(StockQty) as 'Total'
from tblStock