Блог

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

Преобразование таблицы по строкам в таблицу по столбцам

#sql #sql-server #tsql #pivot #dynamic-pivot

#sql #sql-сервер #tsql #сводная #динамический-сводный

Вопрос:

У меня есть таблица, разработанная в формате по столбцам, как показано ниже:

  ------ --------- ------------- ------------ 
| S_ID | S_NAME  | MARK_NUMBER | MARK_VALUE |
 ------ --------- ------------- ------------ 
|    1 | TEST    | MARK1       |         50 |
|    1 | TEST    | MARK2       |         60 |
|    1 | TEST    | MARK3       |         70 |
|    2 |  TEST_! | MARK1       |         40 |
|    2 |  TEST_! | MARK2       |         50 |
|    2 |  TEST_! | MARK3       |         40 |
 ------ --------- ------------- ------------

MARK_NUMBER в столбце будет ‘N’ количество меток. Я не могу определить ее / настроить ее статически (как упоминалось в другом ответе SO). Мне нужно преобразовать эту таблицу в формат по столбцам, как показано ниже:

  S_ID   S_Name  MARK1   MARK2   MARK3
   1    TEST    50        60    70
   2    TEST_!  40        50    40

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

1. Для достижения желаемого результата необходимо использовать сводную таблицу. Поскольку количество меток может составлять N, решением может быть динамический запрос.

Ответ №1:

Попробуйте это:

 CREATE TABLE #DataSource
(
    [S_ID] INT
   ,[S_NAME] VARCHAR(12)
   ,[MARK_NUMBER] VARCHAR(12)
   ,[MARK_VALUE] INT
);

INSERT INTO #DataSource ([S_ID], [S_NAME], [MARK_NUMBER], [MARK_VALUE])
VALUES (1, 'TEST', 'MARK1', 50)
      ,(1, 'TEST', 'MARK2', 60)
      ,(1, 'TEST', 'MARK3', 70)
      ,(2, 'TEST_!', 'MARK1', 40)
      ,(2, 'TEST_!', 'MARK2', 50)
      ,(2, 'TEST_!', 'MARK3', 40);

DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);


SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                SELECT ',['   CAST([MARK_NUMBER] AS VARCHAR(12))   ']'
                                FROM #DataSource
                                GROUP BY [MARK_NUMBER]
                                ORDER BY [MARK_NUMBER]
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );

SET @DynammicTSQLStatement = N'
SELECT *
FROM #DataSource
PIVOT
(
    MAX([MARK_VALUE]) FOR [MARK_NUMBER] IN ('   @DynamicPIVOTColumns   ')
) PVT';

EXEC sp_executesql @DynammicTSQLStatement;

DROP TABLE #DataSource;

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

Ответ №2:

Моим решением был бы динамический СВОД:

 DROP TABLE IF EXISTS #mytemptable
CREATE TABLE #mytemptable
(
   S_ID int
  ,S_NAME varchar(20)
  ,MARK_NUMBER varchar(20)
  ,MARK_VALUE int
)

INSERT INTO #mytemptable VALUES
(1,'TEST   ','MARK1',50),
(1,'TEST   ','MARK2',60),
(1,'TEST   ','MARK3',70),
(2,' TEST_!','MARK1',40),
(2,' TEST_!','MARK2',50),
(2,' TEST_!','MARK3',40)

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns  = N', p.'   QUOTENAME(MARK_NUMBER)
  FROM (SELECT DISTINCT p.MARK_NUMBER FROM #mytemptable AS p) AS x;
SET @sql = N'
SELECT *
FROM
(     select S_ID, S_NAME, MARK_VALUE, MARK_NUMBER from #mytemptable
) AS j
PIVOT
(
  SUM(MARK_VALUE) FOR MARK_NUMBER IN ('
    STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
    ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

DROP TABLE IF EXISTS #mytemptable