SQL-запрос для извлечения одной записи в формате " заголовок столбца: значение столбца"

Предположим, что у меня есть такая таблица: 

Create table test(a int, b int, c int, d int)

Я могу написать просто "select * from test", чтобы получить следующую первую запись: 

A    B    C    D  
1    2    3    4

Но вместо этого я хочу, чтобы это было так (четыре строки для одной записи): 

A: 1  
B: 2  
C: 3  
D: 4
Может ли кто-нибудь помочь мне в этом?

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

1 4
sql sql-server tsql sql-server-2008 unpivot

1 ответ:

Вы можете использовать UNPIVOT для этого в следующей версии имя и значение столбца объединяются вместе, но вы всегда можете отобразить их как отдельные столбцы: 

select col+':'+cast(value as varchar(10)) col
from test
unpivot
(
  value
  for col in (A, B, C, D)
) unpiv

Смотрите SQL Fiddle with Demo

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

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

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

set @query 
  = 'select col+'':''+cast(value as varchar(10)) col
     from test
     unpivot
     (
       value
       for col in ('+ @colsunpivot +')
     ) u'

exec(@query)

См. SQL Fiddle with Демо

Примечание: при использовании UNPIVOT типы данных всех столбцов, которые необходимо преобразовать, должны быть одинаковыми. Так что вам, возможно, придется приводить/преобразовывать данные по мере необходимости.

Edit #1 , так как ваши типы данных различны во всех ваших Столбцах, и вам нужно разорвать их, то вы можете использовать следующий код.

Первая часть получите список столбцов, которые вы хотите удалить динамически: 

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

Вторая часть получает тот же список столбцов, но оборачивает каждый столбец в cast как varchar: 

select @colsUnpivotCast = stuff((select ', cast('+quotename(C.name)+' as varchar(50)) as '+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('test')
         for xml path('')), 1, 1, '')

Тогда ваш последний запрос будет: 

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


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

select @colsUnpivotCast = stuff((select ', cast('+quotename(C.name)+' as varchar(50)) as '+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('test')
         for xml path('')), 1, 1, '')


set @query 
  = 'select col+'':''+value col
     from
    (
      select '+@colsUnpivotCast+'
      from test
    ) src
     unpivot
     (
       value
       for col in ('+ @colsunpivot +')
     ) u'


exec(@query)

Смотрите SQL Fiddle with Demo

Функция UNPIVOT выполняет тот же процесс, что и UNION ALL, который выглядел бы следующим образом: 

select col+':'+value as col
from
(
  select A value, 'A' col
  from test
  union all
  select cast(B as varchar(10)) value, 'B' col
  from test
  union all
  select cast(C as varchar(10)) value, 'C' col
  from test
  union all
  select cast(D as varchar(10)) value, 'D' col
  from test
) src

Смотрите SQL Fiddle with Demo

Результат всех запросов один и тот же: 

|    COL |
----------
|    A:1 |
| B:2.00 |
|    C:3 |
|    D:4 |

Edit #2: using UNPIVOT удаляет любые пустые столбцы, которые могут вызвать некоторые данные для сброса. Если это так, то вы захотите обернуть столбцы с помощью IsNull(), чтобы заменить значения null: 

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


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

select @colsUnpivotCast = stuff((select ', IsNull(cast('+quotename(C.name)+' as varchar(50)), '''') as '+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('test')
         for xml path('')), 1, 1, '')


set @query 
  = 'select col+'':''+value col
     from
    (
      select '+@colsUnpivotCast+'
      from test
    ) src
     unpivot
     (
       value
       for col in ('+ @colsunpivot +')
     ) u'


exec(@query)

Смотрите SQL Fiddle with Demo

Замена нулевых значений, даст такой результат: 

|    COL |
----------
|    A:1 |
| B:2.00 |
|     C: |
|    D:4 |
11