Transpose rows to column

Almost all report writers offer cross-tabulation.

Standard SQL cannot solve the general case (unknown number of columns),
although the cube functionality will do the tabulation for you. However,
have to do the cross-tabulation layout yourself.

"Tuong Do" <tu*****@hotmail.com> wrote in message
news:cm**********@news-02.connect.com.au...

Hi

I have a table with data like this

ID Type
1 A
1 B
1 C
2 A
2 C
How can I tranfer it to this format
ID Type1 Type2 Type3
1 A B C
2 A Null C

try the Case statement...something like this

select ID,
min(case type='A' then 'A' else null) type_A
min(case type='B' then 'B' else null) type_B
group by ID

Tuong Do wrote:

Hi

I have a table with data like this

ID Type
1 A
1 B
1 C
2 A
2 C
How can I tranfer it to this format
ID Type1 Type2 Type3
1 A B C
2 A Null C

Thank for the quick reply,
I will try the create a view with the case statement

<da**********@gmail.com> wrote in message
news:10********************@c13g2000cwb.googlegrou ps.com...

try the Case statement...something like this

select ID,
min(case type='A' then 'A' else null) type_A
min(case type='B' then 'B' else null) type_B
group by ID

Tuong Do wrote:

Hi

I have a table with data like this

ID Type
1 A
1 B
1 C
2 A
2 C
How can I tranfer it to this format
ID Type1 Type2 Type3
1 A B C
2 A Null C

A little generalized way.
You do not need to know values of Type beforehand.
But, still need to set maximum number of Types.

WITH Types AS (
SELECT Type
, ROWNUMBER() OVER(ORDER BY Type) AS rn
FROM (SELECT DISTINCT Type
FROM Transpose
) AS R
)
SELECT ID
, MIN(CASE WHEN rn = 1 THEN a.type END) type1
, MIN(CASE WHEN rn = 2 THEN a.type END) type2
, MIN(CASE WHEN rn = 3 THEN a.type END) type3
, MIN(CASE WHEN rn = 4 THEN a.type END) type4
, MIN(CASE WHEN rn = 5 THEN a.type END) type5
FROM Transpose A
, Types T
WHERE a.type = t.type
GROUP BY
ID
;
------------------------------------------------------------------------------

ID TYPE1 TYPE2 TYPE3 TYPE4 TYPE5
----------- ----- ----- ----- ----- -----
1 A B C - -
2 A - C - -

2 record(s) selected.