asked on

Trying to convert 'horizontal' records into a 'key/value' type of result (SQL Server)

Hello,

I'm trying to convert 'horizontal' rows into 'vertical' rows, but I'm not quite sure how to approach this.

Let's say I have the following data:

-----------------
CompoundSchedule   (table name)
-----------------

CompoundName		RouteName	Schedule 	Dosage
-----------------------------------------------------------------------------------
CompoundA 		IP		QDx3		125.5
CompoundB		IV		QDx4		110.0
CompoundC 		IP		QDx7		115.0

Open in new window

I'm trying to get a more vertical 'key/value' type of result. For example (and ordering doesn't really matter at this point)

Key 		Value
----------------------------
CompoundName	CompoundA
CompoundName	CompoundB
CompoundName	CompoundC
RouteName		IP
RouteName		IV
RouteName		IP
Schedule 		QDx3
Schedule 		QDx4
Schedule 		QDx7
Dosage 		125.5
Dosage 		110.0
Dosage 		115.0

Open in new window

How would I do this, if it's possible? Keep in mind that ordering doesn't matter at all in this case, I'm just looking for the simplest possible way to get a key/value type of results from 'horizontal' records.

Thanks!

ste5an

Please post concise and complete examples. This includes table DDL and sample data INSERT statements as runnable T-SQL script.

It's a simple UNPIVOT:

SELECT	*
FROM	CompoundSchedule   T
UNPIVOT ( Value FOR [Key] in ( CompoundName, RouteName, Schedule, Dosage ) ) U;

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Synan

ASKER

Awesome, thanks!