January 15, 2009 at 1:07 pm
If I currently has a table that looks like this:
ID Key Value
------------------------
1 Name Bill
1 Height 5'9"
1 Weight 160 lbs
2 Name Sam
2 Height 5'10"
2 Weight 200 lbs
How can I transpose it to look like below?
ID Name Height Weight
-------------------------------------
1 Bill 5'9" 160 lbs
2 Sam 5'10" 200 lbs
Thank you 🙂
January 15, 2009 at 1:13 pm
There are a couple of different ways. I like:
select Names.ID, Names.Name, Height, Weight
from
(select ID, Value as Name
from dbo.Table
where Key = 'Name') Names
inner join
(select ID, Value as Height
from dbo.Table
where Key = 'Height') Heights
on Names.ID = Heights.ID
inner join
(select ID, Value as Weight
from dbo.Table
where Key = 'Weight') Weights
on Names.ID = Weights.ID
Another solution is to use Case statements on the Key column, and then aggregates on the Value columns.
If some IDs might not have all the characteristics, it's best to use outer joins. If all IDs have all keys, then inner joins are fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2009 at 6:27 am
Thank you GSquared 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply