By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 1,920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Transpose rows to column

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.