Сведение строк в столбцы в SQL Server

У меня есть набор данных, который выглядит так:

До

FirstName   LastName   Field1   Field2   Field3 ... Field27
---------   --------   ------   ------   ------     -------
 Mark        Smith     A        B        C          D
 John        Baptist   X        T        Y          G
 Tom         Dumm      R        B        B          U

Однако я бы хотел, чтобы данные выглядели так:

После

FirstName   LastName   Field   Value
---------   --------   -----   -----
Mark        Smith      1       A
Mark        Smith      2       B
Mark        Smith      3       C
Mark        Smith      4       D
John        Baptist    1       X
John        Baptist    2       T
John        Baptist    3       Y
John        Baptist    4       G
Tom         Dumm       1       R
Tom         Dumm       2       B
Tom         Dumm       3       B
Tom         Dumm       4       U

Я посмотрел на функцию PIVOT. Это может сработать. Я не слишком уверен. Я не мог понять, как его использовать. Но я не уверен, что сводка может поставить «4» в столбце «Поле». Насколько я понимаю, функция PIVOT просто перенесет значения Field1...Field27 в столбец «Значение».

Я также рассмотрел итерацию по таблице с помощью курсора, а затем цикл по столбцам полей, а затем ВСТАВКУ в другую таблицу «Поля» и «Значение». Однако я знаю, что это повлияет на производительность, поскольку это последовательная операция.

Любая помощь будет принята с благодарностью! Как вы понимаете, я новичок в T-SQL (или SQL в целом) и SQL Server.


sql transpose pivot-table unpivot pivot
person supercoder    schedule 29.05.2012    source источник

Ответы (3)


arrow_upward
4
arrow_downward

Вы можете использовать UNPIVOT. Есть два способа сделать это:

1) В Static Unpivot вы должны жестко закодировать столбцы поля в своем запросе.

select firstname
  , lastname
  , replace(field, 'field', '')  as field
  , value
from test
unpivot
( 
  value
  for field in (field1, field2, field3, field27)
) u

См. SQL Fiddle для рабочей демонстрации.

2) Или вы можете использовать Dynamic Unpivot, который получит список элементов в PIVOT при запуске SQL. Dynamic отлично подходит, если у вас есть большое количество полей, которые вы не сводите.

create table mytest
(
  firstname varchar(5),
  lastname varchar(10),
  field1 varchar(1),
  field2 varchar(1),
  field3 varchar(1),
  field27 varchar(1)
)

insert into mytest values('Mark', 'Smith', 'A', 'B', 'C', 'D')
insert into mytest values('John', 'Baptist', 'X', 'T', 'Y', 'G')
insert into mytest values('Tom', 'Dumm', 'R', 'B', 'B', 'U')

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('mytest') and
               C.name like 'Field%'
         for xml path('')), 1, 1, '')

set @query = 'SELECT firstname, lastname, replace(field, ''field'', '''')  as field, value
            from mytest
            unpivot 
            (
               value
               for field in (' + @cols + ')
            ) p '

execute(@query)

drop table mytest

Оба будут давать одинаковые результаты.

person Taryn    schedule 29.05.2012

arrow_upward
3
arrow_downward

Если вы хотите сделать это запросом, то быстрым и грязным способом будет создание Union

             Select FirstName,LastName,1,Field1
             from table
             UNION ALL
             Select FirstName,LastName,2,Field2
             from table
             . 
             .

И аналогично для всех полей cols

person ejb_guy    schedule 29.05.2012

arrow_upward
1
arrow_downward

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

select firstname, lastname, substring(field,6,2) as field, value
from <yourtablename>
unpivot(value for field in (field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14,field15,field16,field17,field18,field19,field20,field21,field22,field23,field24,field25,field26,field27,field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14,field15,field16,field17,field18,field19,field20,field21,field22,field23,field24,field25,field26,field27)) as unpvt;
person jon Z    schedule 29.05.2012