Pivot Query

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?

Thanks in Advance,

Nithyapriya

Give a look to the PIVOT operator in books online. Your PIVOT clause will look something like:

Code Snippet

pivot ( max(employees) for ProjectID in([CCT1],[CCT2],[CCT3],[CCT4]) ) piv

Your SELECT clause will look something like:

Code Snippet

select unitId,
CCT1,
CCT2,
CCT3,
CCT4
...

There is an alternate "standard method" using CASE and MAX that can be constructed if you are using SQL Server 2000 instead of SQL Server 2005.

|||

Hi,

Try this one:

SELECT

unitid,

[CCT1],

[CCT2],

[CCT3],

[CCT4]

FROM

(

SELECT unitid, projectid, employees FROM EmpProject

) AS Temp PIVOT

(

max(employees)

for projectid in ([CCT1],[CCT2],[CCT3],[CCT4])

) As PV

Thanks & Regards,

Kiran.Y

|||

--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'

SET

NOCOUNT ON

DECLARE

@.T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT

INTO @.T SELECT DISTINCT projectid FROM empproject

DECLARE

@.T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE @.i AS int

SET @.i=1

WHILE @.i <20

BEGIN

INSERT INTO @.T1 SELECT @.i

SET @.i=@.i+1

END

DECLARE @.cols AS nvarchar(MAX), @.cols2 AS nvarchar(MAX),@.y AS nvarchar(20)

SET @.y = (SELECT MIN(y) FROM @.T)

SET @.cols = N''

SET @.cols2 = N''

WHILE @.y IS NOT NULL

BEGIN

SET @.cols = @.cols + N',['+CAST(@.y AS nvarchar(20))+N']'

--SET @.cols2 = @.cols2 + N',ISNULL(['+CAST(@.y AS nvarchar(20))+N'],0) AS ' + N'['+CAST(@.y AS nvarchar(20))+N']'

SET @.y = (SELECT MIN(y) FROM @.T WHERE y > @.y)

END

SET @.cols = SUBSTRING(@.cols, 2, LEN(@.cols))

--SET @.cols2 = SUBSTRING(@.cols2, 2, LEN(@.cols2)-1)

DECLARE @.sql AS nvarchar(MAX)

SET @.sql = N'SELECT unitid, ' +@.cols + N' FROM (

SELECT unitid, employees,projectid

FROM empproject

) as t

PIVOT (MAX(employees) FOR projectid IN(' + @.cols + N')) AS pvt'

EXEC sp_executesql @.sql

--print @.sql

|||

Kiran,

It worked greatly!

Nice work!

Regards,

NithyaPriya