PIVOT Query in SQL Server 2005

A dynamic column list? You will likely need to create your PIVOT query
using dynamic SQL.

The bible of starter pages for dynamic SQL is here:

http://www.sommarskog.se/dynamic_sql.html



"Rahul Chatterjee" wrote in message
news:uhuZAwfiIHA.4744@TK2MSFTNGP06.phx.gbl...
Hello All

I am trying to write a pivot query. My database table looks like this

ID TypeID Amount
2 1 9227
2 2 413.5

select ID,[1] as Gross, [2] as Hours from

(Select distinct ID,typeid,amount from tblContributionInvoiceItemDetail
where id=2) PS

PIVOT (sum(Amount) FOR TypeID in ([1],[2])) as PVT



The above solution works adequately and returns the right results but my
problem is that the typeID field can have many more values and I dont want
to change the query every time I add a value to the table. I would like it
to be dynamic.

Can anyone suggest a solution where I can build a dynamic list?

Thanks

I'm surprised someone hasn't accused you of a design flaw for not having a set number of values for typeID:) If you think there's something to be gained by subtracting the need for writing dynamic sql code check out the RAC utility. It will easily do anything you want if you play your parameters right:)

www.rac4sql.net


www.beyondsql.blogspot.com

"Rahul Chatterjee" wrote in message news:uhuZAwfiIHA.4744@TK2MSFTNGP06.phx.gbl...
Hello All

I am trying to write a pivot query. My database table looks like this

ID TypeID Amount
2 1 9227
2 2 413.5

select ID,[1] as Gross, [2] as Hours from

(Select distinct ID,typeid,amount from tblContributionInvoiceItemDetail where id=2) PS

PIVOT (sum(Amount) FOR TypeID in ([1],[2])) as PVT



The above solution works adequately and returns the right results but my problem is that the typeID field can have many more values and I dont want to change the query every time I add a value to the table. I would like it to be dynamic.

Can anyone suggest a solution where I can build a dynamic list?

Thanks

>> I'm surprised someone hasn't accused you of a design flaw for not having
>> a set number of values for typeID:)

You mean that you can have a type or an ID but it can't be both at the same
time. :-)

A

>> You mean that you can have a type or an ID but it can't be both at the same time. :-) <<

Hey, that is my rant! Maybe the column, which he mistook for a field,
is a "type_id_value_code" ?? Why stop with a bad data element name
when you can go all the way and make the data dictionary totally
useless?

>> Can anyone suggest a solution where I can build a dynamic list? <<

You might want to learn what a table is (fixed number of columns (NOT
fields!), of known domains, etc.) and why they are not reports. The
correct tool is a report writer, not a query.

> Hey, that is my rant! Maybe the column, which he mistook for a field,
> is a "type_id_value_code"

I actually use "TypeCodeValueID"... YMMV. =)

Though, I do think a TypeID can be useful, only storing the
description once, and using a skinnier integer to represent the type in the
main entity table. But that's a different argument, because I know how you
feel about surrogates. Well, surrogate keys, at least.

"Aaron Bertrand [SQL Server MVP]" wrote in message
news:9B231421-C3B0-4EBB-8BE1-0B0A722FCDE0@microsoft.com...
>>> I'm surprised someone hasn't accused you of a design flaw for not having
>>> a set number of values for typeID:)
>
> You mean that you can have a type or an ID but it can't be both at the
> same time. :-)

Good catch! :)