Transpose columns TO Rows - 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

SQL Server Development (2000)

Transpose columns TO Rows

Author

Topic

yaditzal
Starting Member

22 Posts

Posted - 2010-04-05 : 15:54:57

Hi.
any help with this:
I have this table.

prov_id spec1 board_cert spec2 borad_cert
456 urology Y urology-radiology N

I need to split the spec columns in row like this.

prov_id specialty board_cert
456 urology Y
456 urology-radiology N

I need to create a table with many records by provider depending of how many spscialties the provider has.

thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-05 : 17:00:40

How many spec/board_cert column pairs are there in your source table?

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 00:24:24

for the sample you posted, this will do the trick

SELECT prov_id,specialty, board_cert
FROM
(
SELECT prov_id, spec1 AS specialty, board_cert,1 AS SortOrd 
FROM Table
UNION ALL
SELECT prov_id,spec2 borad_cert,2 
FROM Table
)t
ORDER BY prov_id,SortOrd

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaditzal
Starting Member

22 Posts

Posted - 2010-04-06 : 09:15:34

thanks so much it worked perfect.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-06 : 09:20:52

quote:
Originally posted by yaditzal

thanks so much it worked perfect.

Well. Have you read the question asked to you?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 11:50:46

quote:
Originally posted by yaditzal

thanks so much it worked perfect.

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaditzal
Starting Member

22 Posts

Posted - 2010-04-07 : 09:15:09

there are 2 pairs. but thanks any way, I resolvd the problem with the code visakh16 gave me.
Thanks any way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources