May 27, 2010 at 2:00 pm
Hi all,
I have this simply table:
User -- Score
Paul -- 10
Paul -- 15
Paul -- 5
Paul -- 7
If I do : "SELECT * FROM TBL_SCORES ORDER BY SCORE"
I get:
Paul -- 5
Paul -- 7
Paul -- 10
Paul -- 15
I would like to get :
User -- Score1 -- Score2 -- Score3 -- Score4 -- Score5
Paul -- 5 -- 7 -- 10 -- 15 -- 0
Thank you all,
F
May 27, 2010 at 2:54 pm
Check out the two links in my signature for CROSS-TABS and PIVOTS.
If you still need help, check out the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 28, 2010 at 7:48 am
i found error if i put "paul" repetitively
declare @t table ( name nvarchar(30), num int)
insert into @t
select 'paul1', 10 union
select 'paul2', 5 union
select 'paul3', 4 union
select 'paul4', 3
select * from @t
SELECT
[paul1] as S1,
[paul2] as S1,
[paul3] as S3,
[paul4] as S4
FROM
(SELECT name,num
FROM @t) s
PIVOT
(
SUM(num)
FOR name IN ([paul1],[paul2],[paul3],[paul4])
) p
GO
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 1, 2010 at 4:43 am
How many scores are you expecting against Each user. Ist it 5 or Less / More than that?
June 1, 2010 at 4:45 am
Almost same solution posted here...
http://www.sqlservercentral.com/Forums/Topic929079-145-1.aspx
June 1, 2010 at 5:15 am
Modifed Bhuvanesh's solution to output correct results
declare @t table (ScoreID INT, User1 nvarchar(30), Score int)
insert into @t
select 1,'paul', 10 union
select 2,'paul', 5 union
select 3,'paul', 4 union
select 4,'paul', 3
select * from @t
SELECT user1,
[1] as S1,
[2] as S1,
[3] as S3,
[4] as S4
FROM
(SELECT ScoreID,User1,Score
FROM @t
) s
PIVOT
(
MAX(Score)
FOR ScoreID IN ([1],[2],[3],[4])
) p
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply