Sql Server 2008 строка в столбец

У меня есть две таблицы, tempUsers и tempItems. Эти две таблицы имеют отношение один ко многим.

Когда я использую внутреннее соединение для этих двух таблиц, результат выглядит следующим образом: 

**user | Category**
Jack | Shoes
Jack | Tie
Jack | Glass
Peggy | Shoe
Peggy | Skirt
Peggy | Bat
Peggy | Cat
Bruce | Laptop
Bruce | Beer
Chuck | Cell Phone

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

**User | Category1  | Category2 | Category3 | Category4**
Jack   | Shoes      | Tie       | Glass     | .....
Peggy  | Shoe       | Skirt     | Bat       | Cat
Bruce  | Laptop     | Beer      |.....      |......
Chuck  | Cell Phone | .....     |.......    |

Число различных категорий в категории является динамическим - их может быть любое количество для данного элемента.

Как я могу получить этот результат?

3 3
sql-server sql-server-2008 pivot

3 ответа:

Существует несколько способов преобразования данных из строк в столбцы.

Так как вы используете SQL Server 2008, то вы можете использовать функцию PIVOT.

Я бы предложил использовать функцию row_number(), чтобы помочь в повороте данных. Если у вас есть известное число значений, то вы можете жестко запрограммировать запрос: 

select user, category1, category2, category3, category4
from
(
  select [user], category,
    'Category'+cast(row_number() over(partition by [user] 
                                      order by [user]) as varchar(3)) rn
  from yt
) d
pivot
(
  max(category)
  for rn in (category1, category2, category3, category4)
) piv;

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

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

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Category'+cast(row_number() over(partition by [user] 
                                                                      order by [user]) as varchar(3))) 
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [user],' + @cols + ' 
              from
              (
                select [user], category,
                  ''Category''+cast(row_number() over(partition by [user] 
                                                    order by [user]) as varchar(3)) rn
                from yt
              ) d
              pivot 
              (
                  max(category)
                  for rn in (' + @cols + ')
              ) p '

execute(@query)

Смотрите SQL Fiddle with Demo. Оба дают результат: 

|  USER |  CATEGORY1 | CATEGORY2 | CATEGORY3 | CATEGORY4 |
----------------------------------------------------------
| Bruce |     Laptop |      Beer |    (null) |    (null) |
| Chuck | Cell Phone |    (null) |    (null) |    (null) |
|  Jack |      Shoes |       Tie |     Glass |    (null) |
| Peggy |       Shoe |     Skirt |       Bat |       Cat |
2

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

Нормальный решение для поворота с неизвестным числом столбцов-это сделать поворот на стороне клиента, из кода, который вызывает сервер.

0

Вот решение с использованием нескольких таблиц. Это решение полностью основано на решенииbluefeet . Я только что добавил идентификатор пользователя.

create table #tmpUsers
(user_id int, user_name varchar(255));
insert into #tmpUsers values (1,'Jack');
insert into #tmpUsers values (2,'Peggy');
insert into #tmpUsers values (3,'Bruce');
insert into #tmpUsers values (4,'Chuck');


create table #tmpItems
(user_id int, category varchar(255));
insert into #tmpItems values(1,'Shoes');
insert into #tmpItems values(1,'Tie');
insert into #tmpItems values(1,'Glass');

insert into #tmpItems values(2,'Shoe');
insert into #tmpItems values(2,'Skirt');
insert into #tmpItems values(2,'Bat');
insert into #tmpItems values(2,'Cat');

insert into #tmpItems values(3,'Laptop');
insert into #tmpItems values(3,'Beer');

insert into #tmpItems values(4,'Cell Phone');


select TU.user_name,TI.category from #tmpUsers TU inner join #tmpItems TI on TU.user_id=TI.user_id


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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Category'+cast(row_number() over(partition by TU.[user_id] 
                                                                      order by TU.[user_id]) as varchar(3))) 
                    from #tmpUsers TU inner join #tmpItems TI on TU.user_id=TI.user_id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT [user_name],' + @cols + ' 
              from
              (
                select TU.[user_name], TI.category,
                  ''Category''+cast(row_number() over(partition by TU.[user_id] 
                                                    order by TU.[user_id] ) as varchar(3)) rn
                from #tmpUsers TU inner join #tmpItems TI on TU.user_id=TI.user_id
              ) d
              pivot 
              (
                  max(category)
                  for rn in (' + @cols + ')
              ) p '

execute(@query)


drop table #tmpUsers
drop table #tmpItems
0