SQL Server 2008 Pivot Key Value to Joined Parent?

SQL Server 2008, I have the following parent/child table schemata and rows:

   create table #par( parid int primary key identity(1,1) , partext varchar(6) )
create table #chi( chiid int primary key identity(1,1) , parid int null , chirefid int null , chiinfo varchar(6) )
create table #chiref ( chirefid int primary key identity(1,1) , chisubdesc varchar(9) )

insert into #par values ( 'par1' ) , ('par2')

insert into #chiref values ( 'chi1' )
insert into #chiref values ( 'chi2' )
insert into #chiref values ( 'chi3' )

insert into #chi values ( 1 , 1 , 'aaa' )
insert 开发者_如何转开发into #chi values ( 1 , 2 , 'bbb' )
insert into #chi values ( 2 , 1 , 'ccc' )
insert into #chi values ( 2 , 2 , 'ddd' )
insert into #chi values ( 2 , 3 , 'eee' ) 

The child #chi has just key/value pairs inside, and I need to convert the text (key) into a column and put the value inside, so the result set is shaped like the below. What is the best way to do that (I cannot change the key/value stuff, it is inherited from another system). And there is a join on the #chiref table for the actual column names (which is really killing me).

partext     chi1     chi2     chi3
par1        aaa      bbb
par2        ccc      ddd      eee

Thanks.

EDIT - The thing to remember is that the column names have to match the "key value" in the table. So if the EAV row key is "chi1" then the pivot has to have "chi1". I had simply "1" which broke. So I got it.

Thanks again!

---

select partext, [chi1], [chi2], [chi3]
from
(
    select p.partext, c.chitext, c.chiinfo
    from #chi c
        join #par p
            on c.parid = p.parid
) AS SourceTable
pivot
(
    min(chiinfo)
    for chitext in ([chi1], [chi2], [chi3])
) as PivotTable

or this one is a little more efficient, although more code:

with c
as
(
    select parid, [chi1], [chi2], [chi3]
    from
    (
        select parid, chitext, chiinfo
        from #chi
    ) AS SourceTable
    pivot
    (
        min(chiinfo)
        for chitext in ([chi1], [chi2], [chi3])
    ) as PivotTable
)
select p.partext, c.*
from c
join #par p
    on c.parid = p.parid

if you need a dynamic column number, you can do dynamic query:

declare @ColumnList varchar(max) 

select @ColumnList = isnull(@columnList + ', ', '') + '[' + chitext + ']'
from
(
    select distinct chitext
    from #chi
) tt
order by chitext

declare @Command varchar(max) = '
    with c
    as
    (
        select parid, ' + @ColumnList + '
        from
        (
            select parid, chitext, chiinfo
            from #chi
        ) AS SourceTable
        pivot
        (
            min(chiinfo)
            for chitext in (' + @ColumnList + ')
        ) as PivotTable
    )
    select p.partext, ' + @ColumnList + '
    from c
    join #par p
        on c.parid = p.parid
'
execute(@Command)