April 22, 2012 at 8:50 pm
sample data:
drop table #data
create table #data(number int, name varchar(99), comment varchar(99))
insert into #data select 1, 'joe','smart'
insert into #data select 1, 'bill','simple'
insert into #data select 1, 'rob','steals'
insert into #data select 2, 'joe','grateful'
insert into #data select 2, 'bill','homeless'
insert into #data select 3, 'joe','dog'
Required output:
number,joe,bill,rob
1,smart,simple,steals
2,grateful,homeless,NULL
3,dog,NULL,NULL
One method:
select distinct d.number,j.joe,b.bill,r.rob from #data d
left join (select number, comment as [joe] from #data where name = 'joe') j on d.number = j.number
left join (select number, comment as [bill] from #data where name = 'bill') b on d.number = b.number
left join (select number, comment as [rob] from #data where name = 'rob') r on d.number = r.number
But this method is slow.
Can it be done using PIVOT? Is it faster than above ?
April 22, 2012 at 9:17 pm
Do you mind describing in words what the desired outcome represents? Whats the story?
--Quote me
April 22, 2012 at 9:32 pm
Each project (represented by column 'number') has one or more people (column 'name') working, who have their opinion of the project ('comment')
At a glance, management would like to see all comments made by all workers in each project (one row per project, one column per worker).
April 22, 2012 at 10:30 pm
If are you 100% sure that the values for name will alwys be 'joe' , 'bill' and 'rob' (i sense that this is sample data, and your original data will contain a different set of vlaues) , then you can use this:
select *
from #data d
pivot (max(comment) for name in ([joe],[bill],[rob])) pivot_handle
If they are not know at an earlier point of time, then you may have to employ dynamic-Sql to pivot them. Take a gander at this article[/url] from Jeff to learn more about how to do it!
April 23, 2012 at 4:52 am
This definitely looks like a job for dynamic SQL. The thing to remember, though, is that the PIVOT command requires some sort of aggregation, which may skew the result set.
April 23, 2012 at 6:54 am
great! thanks
thought of something similar, but didn't know how/why the min(), max() aggregates would be relevant for strings.
yes, the column names must be dynamic.
Actually, they must come from somewhere else :
create table #colnames(name varchar(99), col varchar(99))
insert into #colnames select 'joe','Joseph'
insert into #colnames select 'bill','William'
insert into #colnames select 'rob','Robert'
Actual required output :
number,Joesph,William,Robert -- note the change to Full names
1,smart,simple,steals
2,grateful,homeless,NULL
3,dog,NULL,NULL
Google keyword search help : how to pivot string or text columns, pivot aggregate function for string or text
April 23, 2012 at 7:57 am
dynamic SQL is the way to go with this.
this is the way I would approach the problem
You need to get the results set for the columns
(SELECT distinct name from Comments) and dump it in a temp table #TT
NAME
Bill
Bob
John
You need to get this results set into a comma separated list
'Bill',Bob','John'
There are a number of ways to do this but in this instance a CURSOR is probably the simplest as the overhead will not be too great.
DECLARE @SQL nvarchar(MAX)
@SQL = your PIVOT query but when you get to the bit that defines the columns you replace it with the string you previously built.
sp_Execute @SQL
Here is an example that uses the STUFF() function to build the string, but I think it is harder to read for noobies..
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply