sql mssql pivot

sql-mssql-Pivot

[code:]sql[:code]with basicTable as
(
       select
             id
             ,prop
             ,val
       from
       (
             select 1 as id, 1 as prop, 10 as val
             union all
             select 1, 2, 12
             union all
             select 1, 3, 10
             union all
             select 1, 4, 10
             union all
             select 1, 5, 12
             union all
             select 1, 6, 10

             union all

             select 2 ,1, 10
             union all
             select 2, 2, 12
             union all
             select 2, 3, 42
             union all
             select 2, 4, 89
             union all
             select 2, 5, 78
             union all
             select 2, 6, 41
       ) t0
), prop as
(
       select
             t0.id
             ,t0.prop
             ,t0.val
             ,t1.name
       from basicTable t0
       inner join
       (
             select
                    id
                    ,name
             from
             (
                    select 1 as id, 'один' as name
                    union all
                    select 2, 'два'
                    union all
                    select 3, 'три'
                    union all
                    select 4, 'четыре'
                    union all
                    select 5, 'пять'
                    union all
                    select 6, 'шесть'
             ) t0
       ) t1 on t0.prop = t1.id
)


select 
        id
       ,[один]
       ,[два]
       ,[три]
       ,[четыре]
       ,[пять]
       ,[шесть]
from (
       select id, val, name  from prop
) p
pivot(
       max(val) for name In(
             [один]
             ,[два]
             ,[три]
             ,[четыре]
             ,[пять]
             ,[шесть]
       )
) pvt

[/code]