Is a dynamic pivot using LINQ possible?

Question

I have a T-SQL 2005 query which returns:

pid         propertyid  displayname     value
----------- ----------- --------------- ---------------
14270790    74          Low Price       1.3614
14270790    75          High Price      0
14270791    74          Low Price       1.3525
14270791    75          High Price      0
14270792    74          Low Price       1.353
14270792    75          High Price      0
14270793    74          Low Price       1.3625
14270793    75          High Price      0
14270794    74          Low Price       1.3524
14270794    75          High Price      0

What I would like to do is essentially pivot on the displayname field, hopefully producing:

pid       Low Price  High Price
14270790  1.3614     0
14270791  1.3525     0
14270792  1.353      0
14270793  1.3625     0
14270794  1.3524     0

(Not sure how the propertyid field would be output, so I left it out (was hoping it would simply sit alongside the Low Price and High Price fields, to indicate their IDs, but I don't think that will work.)

The problem is that the content of the original displayname field is dynamic - it is produced from a join with a PropertyName' table, so the number of pivoted columns is variable. It could therefore containHigh Price,Low Price,OpenandClose`, depending on what the join with that table returns.

It is, of course, relatively easy (regardless of the trouble I'm having writing the initial query!) to produce this pivot in a fixed query or stored proc. However, is it possible to get LINQ to generate a SQL query which would name each column to be produced rather than having to write a dynamic (probably in a stored proc) query which lists out the column names?

Thanks,

Matt.

Answer 1

This is the closest I could get, but it's not LINQ...

create table #t
(
    pointid [int],
    doublevalue [float],
    title [nvarchar](50)
)

insert into #t
    select
        distinct top 100
        v.pointid, v.doublevalue, p.title
    from [property] p
        inner join pointvalue v on p.propertyid = v.propertyid
        inner join point pt on v.pointid = pt.pointid
    where v.pointid in (select top 5 p.pointid from point p where p.instanceid = 36132)

declare @fields nvarchar(250)
set @fields = (select STUFF((SELECT N',[' + title + ']' FROM [property] FOR XML PATH('')), 1, 1, N''))
--select @fields

declare @sql nvarchar(500)
set @sql = 'select * from #t
pivot
(
    sum(doublevalue)
    for [title] in ('+@fields+')
) as alias'
--select @sql

exec (@sql)

drop table #t

The kicker is that I'm simply asking for every entry in the Property table, meaning there's a lot of columns, in the resulting pivot, which have NULL values.