SQL Server: переносить строки в столбцы

Ответ 1

Один из способов сделать это, если значения tagID известны заранее, - использовать условную агрегацию

SELECT TimeSeconds,
       COALESCE(MAX(CASE WHEN TagID = 'A1' THEN Value END), 'n/a') A1,
       COALESCE(MAX(CASE WHEN TagID = 'A2' THEN Value END), 'n/a') A2,
       COALESCE(MAX(CASE WHEN TagID = 'A3' THEN Value END), 'n/a') A3,
       COALESCE(MAX(CASE WHEN TagID = 'A4' THEN Value END), 'n/a') A4
  FROM table1
 GROUP BY TimeSeconds

или если вы в порядке с NULL значениями вместо 'n/a'

SELECT TimeSeconds,
       MAX(CASE WHEN TagID = 'A1' THEN Value END) A1,
       MAX(CASE WHEN TagID = 'A2' THEN Value END) A2,
       MAX(CASE WHEN TagID = 'A3' THEN Value END) A3,
       MAX(CASE WHEN TagID = 'A4' THEN Value END) A4
  FROM table1
 GROUP BY TimeSeconds

или PIVOT

SELECT TimeSeconds, A1, A2, A3, A4
  FROM
(
  SELECT TimeSeconds, TagID, Value
    FROM table1
) s
PIVOT
(
  MAX(Value) FOR TagID IN (A1, A2, A3, A4)
) p

Выход (с NULL s):

TimeSeconds A1      A2     A3    A4
----------- ------- ------ ----- -----
1378700244  3.75    NULL   NULL  NULL
1378700245  30.00   NULL   NULL  NULL
1378700304  1.20    NULL   NULL  NULL
1378700305  NULL    56.00  NULL  NULL
1378700344  NULL    11.00  NULL  NULL
1378700345  NULL    NULL   0.53  NULL
1378700364  4.00    NULL   NULL  NULL
1378700365  14.50   NULL   NULL  NULL
1378700384  144.00  NULL   NULL  10.00

Если вам нужно динамически отображать значения tagID, то используйте динамический SQL

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(TagID)
            FROM Table1
            ORDER BY 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @sql = 'SELECT TimeSeconds, ' + @cols + '
              FROM
            (
              SELECT TimeSeconds, TagID, Value
                FROM table1
            ) s
            PIVOT
            (
              MAX(Value) FOR TagID IN (' + @cols + ')
            ) p'

EXECUTE(@sql)

Ответ 2

В SQL Server есть команда PIVOT, которая может быть тем, что вы ищете.

select * from Tag
pivot (MAX(Value) for TagID in ([A1],[A2],[A3],[A4])) as TagTime;

Если столбцы не являются константами, вам придется объединить это с некоторым динамическим SQL.

DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);

select @columns = substring((Select DISTINCT ',' + QUOTENAME(TagID) FROM Tag FOR XML PATH ('')),2, 1000);

SELECT @sql =

'SELECT *
FROM TAG
PIVOT 
(
  MAX(Value) 
  FOR TagID IN( ' + @columns + ' )) as TagTime;';

 execute(@sql);

Ответ 3

Другим вариантом, который может быть подходящим в этой ситуации, является использование XML

Параметр XML для переноса строк в столбцы в основном является оптимальной версией PIVOT, поскольку он учитывает ограничение динамического столбца.

XML-версия script устраняет это ограничение, используя комбинацию XML Path, динамического T-SQL и некоторых встроенных функций (например, STUFF, QUOTENAME).

Вертикальное расширение

Подобно PIVOT и курсору, новые добавленные политики могут быть восстановлены в XML-версии script без изменения исходного script.

Горизонтальное расширение

В отличие от PIVOT, новые добавленные документы могут отображаться без изменения script.

Прорыв производительности

В терминах ввода-вывода статистика XML-версии script почти похожа на PIVOT - единственное отличие состоит в том, что XML имеет второе сканирование таблицы dtTranspose, но на этот раз из кеша логического кэша чтения.

В этой статье вы можете найти более подробную информацию об этих решениях (включая некоторые фактические примеры T-SQL): https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

Ответ 4

На основе от bluefeet здесь хранится процедура, которая использует динамический sql для генерации транспонированная таблица. Для этого требуется, чтобы все поля были числовыми, за исключением транспонированного столбца (столбец, который будет заголовком в результирующей таблице):

/****** Object:  StoredProcedure [dbo].[SQLTranspose]    Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for     transposing.
-- Parameters: @TableName - Table to transpose
--             @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
--        table and FIeldToTranspose should be written using single quotes
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose] 
  -- Add the parameters for the stored procedure here
  @TableName NVarchar(MAX) = '', 
  @FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  DECLARE @colsUnpivot AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @queryPivot  AS NVARCHAR(MAX),
  @colsPivot as  NVARCHAR(MAX),
  @columnToPivot as NVARCHAR(MAX),
  @tableToPivot as NVARCHAR(MAX), 
  @colsResult as xml

  select @tableToPivot = @TableName;
  select @columnToPivot = @FieldNameTranspose


  select @colsUnpivot = stuff((select ','+quotename(C.name)
       from sys.columns as C
       where C.object_id = object_id(@tableToPivot) and
             C.name <> @columnToPivot 
       for xml path('')), 1, 1, '')

  set @queryPivot = 'SELECT @colsResult = (SELECT  '','' 
                    + quotename('[email protected]+')
                  from '[email protected]+' t
                  where '[email protected]+' <> ''''
          FOR XML PATH(''''), TYPE)'

  exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out

  select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')

  set @query 
    = 'select name, rowid, '[email protected]+'
        from
        (
          select '[email protected]+' , name, value, ROW_NUMBER() over (partition by '[email protected]+' order by '[email protected]+') as rowid
          from '[email protected]+'
          unpivot
          (
            value for name in ('[email protected]+')
          ) unpiv
        ) src
        pivot
        (
          sum(value)
          for '[email protected]+' in ('[email protected]+')
        ) piv
        order by rowid'
  exec(@query)
END

Ответ 5

У меня было несколько другое требование, поэтому мне пришлось выборочно переносить столбцы в строки.

В таблице были столбцы:

create table tbl (ID, PreviousX, PreviousY, CurrentX, CurrentY)

Мне нужны столбцы для Previous и Current, а строки для X и Y. Декартово произведение, сгенерированное на статической таблице, работало красиво, например:

select 
    ID,
    max(case when metric='X' then PreviousX
        case when metric='Y' then PreviousY end) as Previous,
    max(case when metric='X' then CurrentX
        case when metric='Y' then CurrentY end) as Current
from tbl inner join
    /* Cartesian product - transpose by repeating row and 
    picking appropriate metric column for period */
    ( VALUES (1, 'X'), (2, 'Y')) AS x (sort, metric) ON 1=1
group by ID
order by ID, sort