473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Transpose rows to column

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
4 39126
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Chris Smith | last post by:
Experience Posters, Sorry if this is not the right group to post this question. He is my issue; Is there a way without the use of 3rd party controls, to transpose the rows of a datatable to...
68
by: Martin Joergensen | last post by:
Hi, I have some files which has the following content: 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 0 0 0 0 0
3
by: bogtom | last post by:
I have records ID Sku Name Date 2 41 Blair 01/04/03 3 45 John 03/04/03 that should look like... ID 2 3 Sku 41 45
8
by: m.wanstall | last post by:
Hi All, This is similar to a question I asked earlier however this is following a more "correct" way of doing things. I have normalised and summarised an Exchange addressbook (a few thousand...
5
by: stevepl7 | last post by:
I want to make the rows become column headings in an Access table or query. The data changes based on input to a form. It looks like this: A B N1 1 N2 2 N3 3 N4 4 Where A and...
4
by: samitasahu | last post by:
Hi, I m showing College Details in a table in jsp . I want to transpose the column data into rows in the table. How to do that in jsp coding.
1
by: lenygold via DBMonster.com | last post by:
Chris Eaton created a SP to to transpose rows to columns : Here is an example of a rowtocol stored proc that takes a SQL statement as the first paramter, a delimiter as the second parameter and the...
1
by: brionesl | last post by:
Hi, I have seen other threads with the same question but I'm not too familiar with Access and their solutions often include writing VB code or very complex SQL statements. What I want to do is...
5
by: jenniferhelen | last post by:
I have been searching threads for a while and found the instructions listed below many times, however when I get to step 6 and select to save, I always receive the following error, "The information...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.