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

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

;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 строк это было 29s (xml) vs 14s (dynamic);
• может быть, это может быть труднее понять для людей не знакомых с XPath;

плюсы

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

просто чтобы помочь новым читателям, я создал пример, чтобы лучше понять ответ @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;

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 (которая должна быть полями строк). В этом случае я не знаю, сколько полей я поставил на мой стол Б.

Мне нужно было решение для преобразования столбцов в строки в 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 (подзапрос select i,* для xml raw, это преобразует все столбцы в один столбец xml)

затем я перекрестно применяю функцию к каждому XML-атрибуту этого столбец, так что я получаю одну строку на атрибут.

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

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

Я оставляю этот комментарий в этом месте, потому что я хочу сослаться на это объяснение в своем посте о полном триггере аудита, который вы можете найти здесь:https://stackoverflow.com/a/43800286/4160788