August 14, 2008 at 5:19 am
Hi Folks,
I'm trying to transpose some data in sql but using one column in the data to identify data that should be part of the same row.... I've had no luck with pivot queries so far but that said this is my first look in to them!!
anyhow, my data looks somewhat like this at the moment.....
FEATURE TEXT DESTINATIONID
Location Rhodes 1
Weather The weather in Rhodes 1
Beaches Beaches in Rhodes 1
Location Samoa 2
Weather The weather in Samoa 2
Beaches Beaches in Samoa 2
but needs to look like this.....
DestinationID Location Weather Beaches
1 Rhodes The weather in Rhodes Beaches in Rhodes
2 Samoa The weather in Samoa Beaches in Samoa
If anybody can show me how I'd be super grateful!!
Thanks
Sam
August 14, 2008 at 8:24 am
Here's one way to do it:
create table #T (
Feature varchar(50),
[Text] varchar(50),
DestinationID int)
insert into #T (Feature, [Text], DestinationID)
select 'Location','Rhodes', 1 union all
select 'Weather','The weather in Rhodes', 1 union all
select 'Beaches','The beaches in Rhodes', 1 union all
select 'Location','Samoa', 2 union all
select 'Weather','The weather in Samoa', 2 union all
select 'Beaches','The beaches in Samoa', 2
;with CTE as
(select DestinationID,
case
when feature = 'Location' then [Text]
else null
end as Location,
case
when feature = 'Weather' then [Text]
else null
end as Weather,
case
when feature = 'Beaches' then [Text]
else null
end as Beaches
from #T)
select DestinationID, max(location) as Location,
max(weather) as Weather,
max(Beaches) as Beaches
from CTE
group by destinationid
Personally, I recommend doing pivot operations in a front-end application like Excel or Report Services. They're better at it than SQL.
- 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
August 14, 2008 at 3:31 pm
FYI an actual pivot query for this would look like:
[font="Courier New"]SELECT * FROM
(SELECT DestinationID, Feature, Text from #T) t
PIVOT (MAX(Text) FOR Feature IN ([Location], [Weather], [Beaches])) AS pv[/font]
but using Case statements like GSquared did is typically more efficient.
August 14, 2008 at 3:56 pm
Thanks guys, I went with the case method and its working nicely for me, thanks for your help
: )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply