Columns to Rows - Pivot table?

Question

Post reply

Columns to Rows - Pivot table?

Dobbs77

SSC Eights!

Points: 909
More actions
October 20, 2004 at 10:07 am

#60631

Hello All,

Looks like a lot of good help available on this site. I think I found a place to hang. Need some help. Not a TSQL guru. Yet.

Here's my problem. I have this table 3 columns by 3 rows.

A B C

2000 1 535

2000 1 434

2000 1 242

I would like to create a table that list the RuleID Horizontally

A B C C C

2000 1 535 434 242

THX. BPH

BPH

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rajiv-170183 Ten Centuries Points: 1117 More actions · Answer 1

hey BPH

iam not sure whether i understood u correct!

check the following code and tell me if its ok?

select * from reg1

collegeid deptid regis

----------- ----------- -----------

2001 1 101

2001 1 202

2001 1 303

then---->

select collegeid,deptid, min(case when regis='101' then regis end) as 'regis1',

min(case when regis='202' then regis end) as 'regis2',

min(case when regis='303' then regis end) as 'regis3' from reg1 group by collegeid,deptid

collegeid deptid regis1 regis2 regis3

----------- ----------- ----------- ----------- -----------

2001 1 101 202 303

tell me its ok

bye!

Rajiv.

Dobbs77 SSC Eights! Points: 909 More actions · Answer 2

Hi Rajiv,

Your solution gets the horizontal look that I'm trying to achive.

The user did come with more info.

>>>There are many Collegeid.

>>>There are many deptid.

>>>There are up to 900 regid. But no deptid will have more than maybe 10 to 15 regid.

So each row will have unique collegeid, unique deptid, and then several regid listed in the row. Or the regid for a particular collegeid deptid combination, may be null.

I'm beginning to think cursor is needed and have been reading about those and how to implement them.

I appreciate your insight.

BPH