Вопрос по sql, sql-server-2005, sql-server – SQL: динамическое представление с именами столбцов на основе значений столбцов в исходной таблице

5

Учитывая две таблицы примеров здесь:

Tickets Table

ID  User    Description

0   James   This is a support ticket
1   Fred    This is a ticket too

Properties Table

ID  TicketID    Label           Value

0   0           Engineer        Scott
1   1           Engineer        Dale
2   0           Manu            Dell
3   1           Manu            HP
4   0           OS              Windows
5   1           OS              Linux

Как я могу прийти к такой точке зрения:

ID  User    Description                 Engineer    Manu    OS

1   James   This is a support ticket    Scott       Dell    Windows
2   Fred    This is a ticket too        Dale        HP      Linux

Важно отметить, что таблица свойств не всегда будет одинаковой. Некоторые & quot; Билеты & quot; может иметь свойства, которые другие не имеют.

Это вообще возможно?

@AaronBertrand, еще раз, я не согласен с тобой ... но каждый раз, когда мне приходится использовать таблицы свойств, у меня почти всегда возникают проблемы с производительностью, и они, как правило, являются проблемой в повороте. Jeremy Holovacs
@ Джереми, но это не обязательно представляет каждый разfor everyone - В прошлом я пользовался EAV с большим успехом, я даже писал об этом в блоге, потому что у многих людей сложилось общее мнение, что это такая ужасная вещь.sqlblog.com/blogs/aaron_bertrand/archive/2009/11/19/…  Это похоже на курсоры. Они вообще плохие? Да. Они всегда плохие? Точно нет. Aaron Bertrand
Таблицы свойств дьявола. Jeremy Holovacs
@Jeremy, поэтому может жестко кодировать имена свойств как имена столбцов. Оба имеют свое место. Aaron Bertrand

Ваш Ответ

1   ответ
13

PIVOT, При выполнении PIVOT вы можете сделать это одним из двух способов: с помощью Static Pivot, который вы будете кодировать для преобразования строк, или Dynamic Pivot, который создаст список столбцов во время выполнения:

Статическая ось (см.SQL Fiddle for Demo):

select id, [user], [engineer], [manu], [OS]
from 
(
    select t.id
        , t.[user]
        , p.ticketid
        , p.label
        , p.value
    from tickets t
    inner join properties p
        on t.id = p.ticketid
) x
pivot
(
    min(value)
    for label in ([engineer], [manu], [OS])
) p

Или вы можете использовать Dynamic Pivot (см.SQL Fiddle for Demo):

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.label) 
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, [user], ' + @cols + ' from 
             (
                 select t.id
                        , t.[user]
                        , p.ticketid
                        , p.label
                        , p.value
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            ) x
            pivot 
            (
                min(value)
                for label in (' + @cols + ')
            ) p '

execute(@query)

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

Ты мой новый герой. Я хочу, чтобы твои дети. (Dynamic Pivot работает именно так, как мне нужно) Scott Beeson
@NickChammas спасибо за совет, я посмотрю на DBE.SE
Ваш первый пример значительно сложнее моего с такими же результатами, а второй ... динамический sql? Прежде чем идти по этому пути, я думаю, вы хотели бы убедиться, что это требование и как будет использоваться таблица.
Это очень гладко. Мы хотели бы, чтобы вы опубликовалиDBA.SE, (В качестве примечания вы можете удалитьTYPE а также.value() из XML-запроса. Призыв к.value() ненужно безTYPE.)
@JeremyHolovacs Я не согласен с тем, что первая сложнее, чем ваша версия с 3 левыми соединениями. Что касается динамической версии, OP запросил динамическую версию запроса, которая была бы гибкой, и это то, что я предоставил. Всегда есть другие способы ответить на вопрос, не стесняйтесь предложить другой динамичный способ. :)

Похожие вопросы