Problem in formulating the query

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)

Problem in formulating the query

Author

Topic

kgdsamy
Starting Member

1 Post

Posted - 2008-01-20 : 05:37:15

Hi

I have a table named General which has the following data

EID DEPT RANK QUESTION

1 EMP 2 45
1 EMP 3 46
1 EMP 3 47
1 EMP 4 48
1 EMP 2 49

Now, I want the result in the following format

EID DEPT A1 A2 A3 A4 A5
1 EMP 2 3 3 4 2

The basic idea, the same user had chosen different question and given different rank for that. I want all the ranks as A1,A2, A3 etc.
For example sake, i have given only 5 rows for one user. The actual goes upto 20 for as many 1000 users.

Kindly suggest a way formulate the query.

regards,
Kgdsamy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-20 : 07:44:49

I can provide you with one method though it might not be optimised one. Try this and let me know how you got on (code not tested)

DECLARE @ID int,@Str varchar(2000),@Sql varchar(8000)

SELECT @ID=MIN(EID)
FROM General

WHILE @ID IS NOT NULL
BEGIN
SELECT @Str=CAST(QUESTION AS VARCHAR(4))+ ',' AS text()
FROM General
WHERE EID=@ID
FOR XML PATH('')

SELECT @Sql='SELECT * FROM (SELECT EID,DEPT,RANK,QUESTION
FROM General) g
PIVOT
(SUM(RANK)
FOR QUESTION IN (' + LEFT(@Str,LEN(@Str)-1) + ')p'

EXEC(@Sql)

SELECT @ID=MIN(EID)
FROM General
WHERE EID>@ID
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources