Pivot table with dynamic data

Question

Pivot table with dynamic data

sql_novice_2007

Hall of Fame

Points: 3307
More actions
October 24, 2013 at 8:44 am

#284904

Hi I have the following pivot table query that is working fine for static items list. But how can I build this query to do pivot for dynamic values for the column item(items could be 1,58).
Select HomeId, bed,chair, tables, radio
FROM
(
select * from houseitems
) As Temp
PIVOT
(
max([value])
FOR [item] In([bed], [chair], [tables], [radio])
) As pt
The above query will display cost of all items of one home as one single row.
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

In my opinion, cross tabs are a lot easier to do dynamic pivoting. for information on this, check the following article: http://www.sqlservercentral.com/articles/Crosstab/65048/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Deki SSC Veteran Points: 275 More actions · Answer 2

If you need dynamic number of columns, the only way I know to do it is with dynamic sql.