Help in pivot - 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 2005 Forums

Transact-SQL (2005)

Help in pivot

Author

Topic

prakashdgl
Starting Member

1 Post

Posted - 2007-09-12 : 03:22:14

Hi,
I have a table EmpProject with following:
=========================
unitid projectid employees
----------- --------- -----------------------
1 CCT1 Ravi,Raja,Kanna
2 CCT1 John,Vijay,Nithya
1 CCT2 Senthil,Lee,Suresh
2 CCT2 Ram,Krish,Latha
2 CCT3 Raja,Vijay,Ram
2 CCT4 Sankar

TSql to create the above :
create table empproject (unitid int, projectid varchar(4), employees varchar(1000))
insert empproject select 1,'CCT1', 'Ravi,Raja,Kanna'
insert empproject select 2,'CCT1', 'John,Vijay,Nithya'
insert empproject select 1,'CCT2', 'Senthil,Lee,Suresh'
insert empproject select 2,'CCT2', 'Ram,Krish,Latha'
insert empproject select 2,'CCT3', 'Raja,Vijay,Ram'
insert empproject select 2,'CCT4', 'Sankar'

I would like to have it in the following format
====================================================
unitid CCT1 CCT2 CCT3 CCT4
---------------------------------------------------------------------------------------------
1 Ravi,Raja,Kanna Senthil,Lee,Suresh
2 John,Vijay,Nithya Ram,Krish,Latha Raja,Vijay,Ram Sankar

Is there any way to query in select statement?

Thanks in Advance,

Prakash

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 03:24:18

Are those different columns, or are they concatenated?
If they are concatenated, have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2007-09-12 : 08:30:17

Hi,

DECLARE @sourceTbl TABLE
(
ID int,
Word varchar(50)
)
insert into @sourceTbl(Id,Word)
select 1,'Ch' union all
select 1,'Ranga' union all
select 1,'nath' union all
select 2,'Ch' union all
select 2,'Keerthi' union all
select 2,'sai' union all
select 3,'Ch' union all
select 3,'Anjaneya' union all
select 3,'Kumar'

declare @tempWords varchar(200)
declare @id int

update @sourceTbl set @tempWords = Word = CASE WHEN Id = @id THEN @tempWords + ', ' + Word ELSE Word END ,
@id = id
select max(Word),id From @sourceTbl group by id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-12 : 08:32:40

It would be only for display
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources