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

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" <tuongdo@hotmail.com> wrote in message
news:cmcgbd$kmg$1@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

<dariuscooper@gmail.com> wrote in message
news:1099597298.559924.4900@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.