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 similar
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

Ian

can 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.

Show quoteHide quote

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 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.

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 the
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

Show quoteHide quote

can't delete a row from primary table - is there an SP for this?
Ranking Rows
T-SQL
Convert(datetime, '07/11/48') Returns Year 2048!!
UPDLOCK blocking readers