Блог

Главная - Вопросы по программированию - Группируйте по столбцам и нескольким строкам в одну строку несколько столбцов

Группируйте по столбцам и нескольким строкам в одну строку несколько столбцов

#sql-server #sql-server-2012 #pivot

Вопрос:

Пожалуйста, помогите мне в этом:

Я хочу сгруппировать по типу теста столбца, но результат должен быть разделен на столбцы, если у них один и тот же тип теста

 CREATE TABLE Result(WorkOrder varchar(10), TestType varchar(20), Result decimal(10,2));
INSERT INTO Result (WorkOrder, TestType, Result) VALUES 
('HP19002316','VitaminA', 10.3),
('HP19002316','VitaminA', 11.3),
('HP19002316','VitaminA', 12.3),
('HP19002316','VitaminB', 13.4),
('HP19002316','VitaminB', 14.4),
('HP19002316','VitaminC', 15.5),
('HP19002316','VitaminD', 17.0)

Я хочу, чтобы SQL возвращал данные в этом формате

 WorkOrder       TestType        Result1   Result2  Result3 
==========================================================
HP19002316      VitaminA        10.3        11.3    12.3    
HP19002316      VitaminB        13.4        14.4    NULL
HP19002316      VitaminC        15.5        NULL    NULL
HP19002316      VitaminD        17.0        NULL    NULL

Столбец Результат# должен быть динамическим, потому что каждый тип теста имеет много результатов

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

1. Ответ: Вам нужен динамический SQL, если вы хотите, чтобы количество столбцов было переменным. Обычный SQL не может этого сделать.

2. «Я хочу» — это не вопрос. В чем вам нужна помощь? Как насчет ваших попыток, которые не сработали? Каковы ваши попытки?

3. Также, что в ваших данных обозначает, что такое «Результат 1», «Результат 2» и «Результат 3»? У вас нет ключа всегда по возрастанию, так что определяет «порядок»?

4. Однако то, что вам здесь нужно, называется Сводной или Перекрестной вкладкой (условная агрегация). Скорее всего, это будет сочетаться с ROW_NUMBER . Как предполагает @TimBiegeleisen, вам также понадобится динамический SQL, если a TestType имеет неопределимое максимальное количество строк.

5. Спасибо, ребята, Перед публикацией я пробовал использовать динамический SQL и pivot, но это не помогло. Мы будем очень признательны, если у вас есть какие-либо рекомендации. Заранее спасибо.

Ответ №1:

Как я уже упоминал в комментариях, здесь вам нужна PIVOT вкладка или Крест; Я предпочитаю последнее, поэтому я собираюсь использовать.

Нединамическое решение этой проблемы было бы следующим:

 WITH RNs AS(
    SELECT WorkOrder,
           TestType,
           Result,
           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column
    FROM dbo.Result)
SELECT WorkOrder,
       TestType,
       MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
       MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
       MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
         TestType;

Проблема, однако, в том, что это «блокирует» вас на 3 результата, но вы предполагаете, что существует неопределенное количество результатов. Поэтому вам нужно динамичное решение.

Приведенное ниже будет работать до 100 результатов. если вам действительно нужно больше столбцов, чем чем, добавьте больше CROSS JOIN s N в CTE Tally . Это приводит к чему-то вроде этого (что довольно грязно).

 DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10),
        @MaxTally int;

SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
      FROM dbo.Result
      GROUP BY WorkOrder,
               TestType) R;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 rows, add more Ns for more rows
SELECT @SQL = N'WITH RNs AS('   @CRLF  
              N'    SELECT WorkOrder,'   @CRLF  
              N'           TestType,'   @CRLF  
              N'           Result,'   @CRLF  
              N'           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column'   @CRLF  
              N'    FROM dbo.Result)'   @CRLF  
              N'SELECT WorkOrder,'   @CRLF  
              N'       TestType,'   @CRLF  
              --Using FOR XML PATH due to not knowing SQL Server version
              STUFF((SELECT N','   @CRLF  
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'')   @CRLF  
              N'FROM RNs R'   @CRLF  
              N'GROUP BY WorkOrder,'   @CRLF  
              N'         TestType;';

PRINT @SQL; --Your best friend.

EXEC sys.sp_executesql @SQL;

Ответ №2:

Вы можете попробовать это и расширить логику.

 select *,((select Result from (select TestType,Result, ROW_NUMBER() over ( PARTITION BY testtype ORDER BY  Testtype) Id from Result) a where a.Id='1' and a.TestType=b.TestType )) Result1,
((select Result from (select TestType,Result, ROW_NUMBER() over ( PARTITION BY testtype ORDER BY  Testtype) Id from Result) a where a.Id='2' and a.TestType=b.TestType )) Result2,
((select Result from (select TestType,Result, ROW_NUMBER() over ( PARTITION BY testtype ORDER BY  Testtype) Id from Result) a where a.Id='3' and a.TestType=b.TestType )) Result3
 from (
select Max(WorkOrder) WorkOrder,TestType from Result group by TestType ) b

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

1. Использование подзапроса для каждого результата кажется здесь излишним. Это приводит к 4 сканированиям таблицы всего для 3 столбцов и не масштабируется по мере добавления новых результатов.

2. Большое вам спасибо @Gnyasha, вы мне очень помогли

3. Зачем использовать нединамический метод, для которого требуется 4 сканирования вместо 1, @PeterNguyNguyen ?

Метки: