Блог

Главная - Вопросы по программированию - Как СГРУППИРОВАТЬ по нулевым значениям в динамической сводке

Как СГРУППИРОВАТЬ по нулевым значениям в динамической сводке

#sql-server #tsql #pivot

Вопрос:

Это мой образец запроса:

 IF OBJECT_ID('tempdb..##A') IS NOT NULL DROP TABLE ##A
IF OBJECT_ID('tempdb..#table1') IS NOT NULL DROP TABLE #table1

Create table #Table1 ( ControlNo INT, Line varchar(50), Profit INT, Code varchar(50) )

INSERT INTO #Table1 (ControlNo, Line, Profit, Code) 
    VALUES  (1111,'Line1',NULL,NULL), 
            (1111,'Line2',100,'A'), 
            (3333,'Line1',200,'C'), 
            (4444,'Line1',50,'B'), 
            (4444,'Line2',100,NULL)
DECLARE @columns AS NVARCHAR(MAX), 
        @finalquery AS NVARCHAR(MAX);

SET @columns = STUFF((SELECT distinct ','   QUOTENAME(Line) FROM #Table1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @finalquery = '
        select  p.ControlNo,
                p.Code,'   @columns   '
        from ( select ControlNo, Line, Code, Profit
                from #Table1
            )a 
    pivot 
    ( 
     SUM(Profit) 
     for Line IN ('   @columns   ') 
    )p 
    ORDER BY ControlNo
    '
exec(@finalquery)

Результат содержит строки с NULL буквами «s».

 ControlNo   Code    Line1   Line2
 1111       NULL    NULL    NULL
 1111        A      NULL    100
 3333        C      200    NULL
 4444       NULL    NULL    100
 4444        B       50     NULL

Желаемый результат должен быть таким:

введите описание изображения здесь

Комментарии:

1. Я не понимаю: как код строки = B попадает в строку 2 = 100, если у 4444 NULL NULL 100 него нет кода?

Ответ №1:

Похоже, что регулярное условное агрегирование здесь будет работать лучше, потому Code что, по-видимому, только MIN(Code) для ControlNo 

 DECLARE @columns nvarchar(max) =
  STUFF((SELECT distinct
        ',ISNULL(SUM(CASE WHEN Line = '   QUOTENAME(Line, '''')   ' THEN Profit END), 0) '   QUOTENAME(Line)
        FROM #Table1
        FOR XML PATH(''), TYPE
        ).value('text()[1]', 'NVARCHAR(MAX)') ,1 , LEN(','),'');

DECLARE @finalquery nvarchar(max)= N'
SELECT
  t.ControlNo,
  MIN(t.Code) Code,
  '   @columns   '
from #Table1 t
GROUP BY
  t.ControlNo
ORDER BY ControlNo;
';
    
PRINT @finalquery; -- for testing

EXEC sp_executesql
  @finalquery;

db<>скрипка

Метки: