All about the PIVOT - HELP!!

Hi all,
I have a problem whereby i need to convert multiple row data into a single
row for instance if i have a table
CliID Code V D T
1 A 100
1 B 01/01/06
1 C Beer
2 A 50
2 C Milk
I would want to return this as
CliID A-V A-D A-T B-V B-D B-T C-V C-D C-T
1 100 01/01/06 Beer
2 50 Milk
I have been able to achieve this using the follow code
select CliID,
max(case when code = 'A' then V else 0 end) as A-V,
max(case when code = 'A' then D else null end) as A-D,
max(case when code = 'A' then T else null end) as A-T,
max(case when code = 'B' then V else 0 end) as B-V,
max(case when code = 'B' then D else null end) as B-D,
max(case when code = 'B' then T else null end) as B-T
max(case when code = 'C' then V else 0 end) as C-V,
max(case when code = 'C' then D else null end) as C-D,
max(case when code = 'C' then T else null end) as C-T
from casdet
group by CliID
which works fine however we are now using sql 2005 and i thought it may be
more efficient to make use of the PIVOT function everyone is talking about.
I
can get a basic pivot working for instance pivoting on the code and
sumarising a column say the V column, however i cannot get it to add in the
additional columns.. the code i am currently using looks like this
select CliID, [A] as A, [C] as C
from
(select CliID, code, V, T from casdet) p
Pivot(
max (V)
for code in
( [A], [C])
) as pvt
This pivots the codes and summarised the V column, for all codes. What i
want to do is pivot the codes as column headers then for each code display
the associated V D and T columns all on a single row.
I know this may sound a little confusing so to summarise.. I have a table
with ID, Code, Value, Date, Text columns.. for a given id i need to produce
a
single row where the code and each of its associated Value, Date, and Text
columns appear on a single row. So hopefully my result set would look simila
r
to
code A Code B Code C
ID V D T V D T V D T
1 1 - - - X - - - A
2 4 - C - Z - - - F
I would like to use the pivot command if possible to do this.
Thanks
Iancan you post the ddl, insert script for sample data and the result for the
sample data?|||ok this is going to seem like a very stupid question but how do i do this.
The data i have given in the above is all made up by way of an example. But
in order to assist I have done a select into statement on the live data to
another table. I can script this but when i do so, all i get is the create
table element or the insert script (depending which one i choose) but i
cannot seem to get it to generate a script to create the table with the
data.. I am sure this was possible when i last used SQL several years back.
"Omnibuzz" wrote:

> can you post the ddl, insert script for sample data and the result for the
> sample data?|||Well,
To answer to this post, have a look at this..
http://www.aspfaq.com/etiquette.asp?id=5006
And, anyways, I tried to create the table with the sample data and tried to
get the results.
But looks like you are better off in using your old syntax than using PIVOT.
PIVOT doesn't address this issue you are facing since you are pivoting on th
e
code and grouping on CliId, we it get complicated and results in lots of
nesting..
Anyways if you want to use that then it goes this way.
select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
from casdet pivot (
max(T) for code in ([a],[b],[c])
) as T,
casdet pivot (
max(D) for code in ([a],[b],[c])
) as D,
casdet pivot (
max(V) for code in ([a],[b],[c])
) as V
where t.cliid = d.cliid and d.cliid = v.cliid
group by T.CliID
And it performs slower :)
Hope this helps.|||Thanks for the reply and the info on how to create the DDL and sample data..
It will help a lot. The only reason i wanted to use the PIVOT method over th
e
older way was I figured that with it being a new feature in SQL2005 it would
be optimised and provide better performance than the older way.
Thanks again
Ian
"Omnibuzz" wrote:

> Well,
> To answer to this post, have a look at this..
> http://www.aspfaq.com/etiquette.asp?id=5006
> And, anyways, I tried to create the table with the sample data and tried t
o
> get the results.
> But looks like you are better off in using your old syntax than using PIVO
T.
> PIVOT doesn't address this issue you are facing since you are pivoting on
the
> code and grouping on CliId, we it get complicated and results in lots of
> nesting..
> Anyways if you want to use that then it goes this way.
>
> select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
> ,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
> max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
> from casdet pivot (
> max(T) for code in ([a],[b],[c])
> ) as T,
> casdet pivot (
> max(D) for code in ([a],[b],[c])
> ) as D,
> casdet pivot (
> max(V) for code in ([a],[b],[c])
> ) as V
> where t.cliid = d.cliid and d.cliid = v.cliid
> group by T.CliID
> And it performs slower :)
> Hope this helps.
>