Crosstab - sorta - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Crosstab - sorta

Author

Topic

sqldataguy
Starting Member

12 Posts

Posted - 2007-04-24 : 12:04:37

Hi, this is my first post to this forum. Please tell me if violate any norms or conventions. I want to be a good citizen!

Here is my problem. I have a simple table of customer IDs and related Product Codes. Each customer can have as many rows as they have products. I need to turn this into a table where each customer has just one row, and their associated products go into a series of fields.

I use a testbed database called SANDBOX for learning and examples. Here is the code that would set up the orginal table.

USE SANDBOX

CREATE TABLE TBL_TEST (CUST_ID VARCHAR(3),PROD_CODE VARCHAR(1))

INSERT INTO TBL_TEST VALUES (100,'A')
INSERT INTO TBL_TEST VALUES (100,'B')
INSERT INTO TBL_TEST VALUES (100,'D')
INSERT INTO TBL_TEST VALUES (200,'A')
INSERT INTO TBL_TEST VALUES (200,'C')
INSERT INTO TBL_TEST VALUES (300,'B')
INSERT INTO TBL_TEST VALUES (400,'A')
INSERT INTO TBL_TEST VALUES (400,'D')

SELECT * FROM TBL_TEST

Now I need a table that would like this:
Cust_ID PC1 PC2 PC3 PC4
100 A B D
200 A C
300 B
400 A D

Appreciate any help! Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 12:08:24

[code]

select CUST_ID,
PC1 = max(case when row = 1 then PROD_CODE end),
PC2 = max(case when row = 2 then PROD_CODE end),
PC3 = max(case when row = 3 then PROD_CODE end),
PC4 = max(case when row = 4 then PROD_CODE end)
from
(
select CUST_ID, PROD_CODE,
row = (select count(*) from TBL_TEST x where x.CUST_ID = t.CUST_ID and x.PROD_CODE <= t.PROD_CODE)
from TBL_TEST t
) a
GROUP BY CUST_ID
[/code]

sqldataguy
Starting Member

12 Posts

Posted - 2007-04-24 : 13:49:07

Sweet! Works perfectly!

Now, would you mind explaining it to me? What is it doing? TIA Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 18:31:25

this section

	select	CUST_ID, PROD_CODE,
		row	= (select count(*) from TBL_TEST x where x.CUST_ID = t.CUST_ID and x.PROD_CODE <= t.PROD_CODE)
	from	TBL_TEST t

gives a seq no to each line. Run the above code and see the result.
The outer section just lists the rows for each CUST_ID. For each CUST_ID, inspect the row value. If it is 1, show in the PC1 column, 2 then PC2 column etc. This is done using CASE . . . WHEN. And together, it gives you what you want.

sqldataguy
Starting Member

12 Posts

Posted - 2007-04-24 : 18:54:07

Yes, thank you again. I was thrown for a bit, thinking "row" was some function I'd never heard of. Once I got that straight I better understood. I really appreciate the help!

I'm right now building this into my real world application. Hopefully I undertand it well enough to use it properly.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 18:59:21

if you are using SQL 2005, you can make use of the row_number() function

sqldataguy
Starting Member

12 Posts

Posted - 2007-04-25 : 09:59:12

Alas, still on 2000. But thanks. I was reading through Ken Henderson's book and saw row_number. I was wondering about that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources