SQL Server: столбцы в строки

Ищете элегантное (или любое) решение для преобразования столбцов в строки.

Вот пример: у меня есть таблица со следующей схемой:

[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

Вот что я хочу получить в результате:

[ID] [EntityId] [IndicatorName] [IndicatorValue]

И значения результата будут:

1 1 'Indicator1' 'Value of Indicator 1 for entity 1'
2 1 'Indicator2' 'Value of Indicator 2 for entity 1'
3 1 'Indicator3' 'Value of Indicator 3 for entity 1'
4 2 'Indicator1' 'Value of Indicator 1 for entity 2'

И так далее..

Имеет ли это смысл? Есть ли у вас какие-либо предложения о том, где искать и как это сделать в T-SQL?

Источник

7 ответов

Решение

Вы можете использовать функцию UNPIVOT для преобразования столбцов в строки:

select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

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

Вы также можете использовать CROSS APPLY с помощью UNION ALL для преобразования столбцов:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  select 'Indicator1', Indicator1 union all
  select 'Indicator2', Indicator2 union all
  select 'Indicator3', Indicator3 union all
  select 'Indicator4', Indicator4 
) c (indicatorname, indicatorvalue);

В зависимости от вашей версии SQL Server вы даже можете использовать CROSS APPLY с предложением VALUES:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

Наконец, если у вас есть 150 столбцов, которые нужно отключить, и вы не хотите жестко кодировать весь запрос, вы можете сгенерировать оператор sql, используя динамический SQL:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'Indicator%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select id, entityId,
        indicatorname,
        indicatorvalue
     from yourtable
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;
Источник

Хорошо, если у вас есть 150 столбцов, то я думаю, что UNPIVOT не вариант. Таким образом, вы могли бы использовать трюк XML

;with CTE1 as (
    select ID, EntityID, (select t.* for xml raw('row'), type) as Data
    from temp1 as t
), CTE2 as (
    select
         C.id, C.EntityID,
         F.C.value('local-name(.)', 'nvarchar(128)') as IndicatorName,
         F.C.value('.', 'nvarchar(max)') as IndicatorValue
    from CTE1 as c
        outer apply c.Data.nodes('row/@*') as F(C)
)
select * from CTE2 where IndicatorName like 'Indicator%'

sql fiddle demo

Вы также можете написать динамический SQL, но мне больше нравится xml - для динамического SQL у вас должны быть права на выбор данных непосредственно из таблицы, и это не всегда вариант.

ОБНОВИТЬ
Поскольку в комментариях есть большое пламя, я думаю, что я добавлю некоторые плюсы и минусы XML / динамического SQL. Я постараюсь быть максимально объективным и не буду упоминать об элегантности и безобразии. Если у вас есть другие плюсы и минусы, отредактируйте ответ или напишите в комментариях

минусы

  • это не так быстро, как динамический SQL, грубые тесты показали, что xml примерно в 2,5 раза медленнее, чем динамический (это был один запрос к таблице ~250000 строк, поэтому эта оценка не является точной). Вы можете сравнить это сами, если хотите, вот пример sqlfiddle, на 100000 строк это было 29 с (xml) против 14 с (динамическое);
  • может быть, это будет сложнее понять людям, не знакомым с xpath;

профи

  • это та же область, что и другие ваши запросы, и это может быть очень удобно. Несколько примеров приходят на ум
    • вы могли бы запросить inserted а также deleted таблицы внутри вашего триггера (вообще невозможно с динамическим);
    • пользователь не должен иметь разрешения на прямой выбор из таблицы. Я имею в виду, что если у вас есть слой хранимых процедур, а у пользователя есть разрешения на запуск sp, но у вас нет прав на прямой запрос таблиц, вы все равно можете использовать этот запрос внутри хранимой процедуры;
    • Вы можете запросить переменную таблицы, которую вы заполнили в своей области (чтобы передать ее в динамический SQL, вам нужно либо вместо этого сделать ее временной таблицей, либо создать тип и передать ее в качестве параметра в динамический SQL;
  • Вы можете сделать этот запрос внутри функции (скалярной или табличной). Невозможно использовать динамический SQL внутри функций;
Источник

Просто потому, что я не видел об этом упоминания.

Если 2016+, вот еще один вариант динамической отмены поворота данных без фактического использования динамического SQL.

пример

Declare @YourTable Table ([ID] varchar(50),[Col1] varchar(50),[Col2] varchar(50))
Insert Into @YourTable Values 
 (1,'A','B')
,(2,'R','C')
,(3,'X','D')

Select A.[ID]
      ,Item  = B.[Key]
      ,Value = B.[Value]
 From  @YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('ID','Other','Columns','ToExclude')
             ) B

Возврат

ID  Item    Value
1   Col1    A
1   Col2    B
2   Col1    R
2   Col2    C
3   Col1    X
3   Col2    D
Источник

Просто, чтобы помочь новым читателям, я создал пример, чтобы лучше понять ответ @bluefeet о UNPIVOT.

 SELECT id
        ,entityId
        ,indicatorname
        ,indicatorvalue
  FROM (VALUES
        (1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),
        (2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),
        (3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),
        (4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')
       ) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
UNPIVOT
(
    indicatorvalue
    FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) UNPIV;
Источник

Мне нужно было решение для преобразования столбцов в строки в Microsoft SQL Server, не зная имен столбцов (используемых в триггере) и без динамического sql (динамический sql слишком медленный для использования в триггере).

Я наконец нашел это решение, которое отлично работает:

SELECT
    insRowTbl.PK,
    insRowTbl.Username,
    attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
    attr.insRow.value('.', 'nvarchar(max)') as FieldValue 
FROM ( Select      
          i.ID as PK,
          i.LastModifiedBy as Username,
          convert(xml, (select i.* for xml raw)) as insRowCol
       FROM inserted as i
     ) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)

Как вы можете видеть, я преобразую строку в XML (подзапрос выберите i,* для необработанного XML, это преобразует все столбцы в один столбец XML)

Затем я КРЕСТУЮ ПРИМЕНИТЬ функцию к каждому атрибуту XML этого столбца, чтобы получить по одной строке для каждого атрибута.

В целом, это преобразует столбцы в строки, не зная имен столбцов и не используя динамический sql. Это достаточно быстро для моей цели.

(Edit: я только что видел ответ Романа Пекара выше, который делает то же самое. Сначала я использовал динамический sql триггер с курсорами, который был в 10-100 раз медленнее, чем это решение, но, возможно, это было вызвано курсором, а не Во всяком случае, это решение очень простое универсальное, так что его, безусловно, вариант).

Я оставляю этот комментарий в этом месте, потому что я хочу сослаться на это объяснение в моем посте о полном триггере аудита, который вы можете найти здесь: /questions/44601107/sozdanie-triggerov-audita-v-sql-server/44601154#44601154

Источник 
DECLARE @TableName nvarchar(50)
DECLARE column_to_row CURSOR FOR

--List of tables that we want to unpivot columns as row
SELECT DISTINCT t.name FROM sys.tables t
JOIN sys.schemas s ON t.schema_id=t.schema_id
WHERE t.name like '%_CT%'
AND s.name='cdc'

OPEN  column_to_row
FETCH NEXT FROM column_to_row INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @script nvarchar(max) = null
DECLARE @columns nvarchar(2000) = null

-- keep the table's column list
select @columns = COALESCE(@columns + ',','') + c.name from sys.tables  t
join sys.columns c on t.object_id = c.object_id
where t.name = @TableName

set @script = 'SELECT '+@columns+' FROM [cdc].['+@TableName+'] (nolock)'
--print (@script)
exec (@script)

FETCH NEXT FROM column_to_row INTO @TableName
END
CLOSE column_to_row
DEALLOCATE column_to_row

Вот еще один метод для столбцов в строках, сколько у вас таблицы и сколько у вас столбцов, это не важно. Просто установите параметры и получите результат. Я написал это, потому что иногда мне нужен результат таблицы A (которая является набором результатов столбца), как поля другой таблицы B (которая должна быть полями строки). В этом случае я не знаю, сколько полей я установил для своей таблицы B.

Источник

Противоположным этому является преобразование столбца в CSV, например

ВЫБЕРИТЕ STRING_AGG ([значение],',') ИЗ STRING_SPLIT('Акио, Хираку, Кадзуо', ',')

Источник
Другие вопросы по тегам