How to pivot this report? - 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 2000 Forums

Transact-SQL (2000)

How to pivot this report?

Author

Topic

SQLCM
Starting Member

13 Posts

Posted - 2007-06-05 : 19:59:57

This is the result I got from running a simple select query:

employee_ID last_name test_date test_score
34 Taylor 02/05/2006 85
85 Lomeli 03/15/2006 72
85 Lomeli 03/25/2006 78
110 smith 03/17/2006 90
110 smith 03/24/2006 89
110 smith 05/05/2006 92

How do I go from this format to the format below?
I would like the result to look like:

employee_ID last_name test_score_1 test_score_2
34 Taylor 85
85 Lomeli 72 78
110 Smith 90 89

Instead of having multiple rows for each employee, I would like to have one row for each empolyee and display up to two test_scores in a single row. (employee can have minimun of 1 test score, maximun of 3 test score)

Thanks for your expertise in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-05 : 20:30:23

you want to display up to 2 test_scores in a single row, but an employee can have 3 of them? What do you want to display when there is 3? Any random 2?

- Jeff
http://weblogs.sqlteam.com/JeffS

SQLCM
Starting Member

13 Posts

Posted - 2007-06-05 : 20:39:23

Jeff,
If an empolyee has 3 test scores, I would like to display top 2 test scores

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 01:10:46

[code]
declare @table table
(
employee_ID int,
last_name varchar(10),
test_date datetime,
test_score int
)
insert into @table
select 34, 'Taylor', '02/05/2006', 85 union all
select 85, 'Lomeli', '03/15/2006', 72 union all
select 85, 'Lomeli', '03/25/2006', 78 union all
select 110, 'smith', '03/17/2006', 90 union all
select 110, 'smith', '03/24/2006', 89 union all
select 110, 'smith', '05/05/2006', 92

select employee_ID, last_name,
test_score_1 = (select max(test_score) from @table x where x.employee_ID = t.employee_ID),
test_score_2 = (select max(test_score) from @table x where x.employee_ID = t.employee_ID
and x.test_score <> (select max(test_score) from @table y where y.employee_ID = x.employee_ID))
from @table t
group by employee_ID, last_name
[/code]

SQLCM
Starting Member

13 Posts

Posted - 2007-06-06 : 01:30:42

Thanks Khtan. But what if I have 13000 employees?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 01:36:46

what is your concern ? performance ?

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2007-06-06 : 11:17:04

CREATE A FUNCTION:
create function dbo.mergerows (@employeeid int)
returns varchar(200)
as
begin
declare @score varchar(1000)
set @score=''
select @score=@score+space(1)+convert(varchar(5),test_score) from mytable
where employee_id=@employeeid
return @score
end
USE THE FUNCTION FOR THE TABLE:
select dbo.mergerows(employee_id)
from mytable

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-06-06 : 12:46:09

try this ...
-- consider ur table here / output given by u
Select * From Mahesh

o/p
employee_id last_name test_score
34 Taylor 85
85 Lomeli 72
85 Lomeli 78
110 smith 90
110 smith 89
110 smith 92

Select employee_id, last_name,
Stuff((Select convert(varchar, test_score) + ', ' From Mahesh As M2
Where M2.employee_id = M1.employee_id Group By test_score FOR XML PATH('')), 1, 0, '')
As CSV_test_score
From Mahesh As M1 Group By employee_id, last_name

o/p
employee_id last_name CSV_test_score
34 Taylor 85,
85 Lomeli 72, 78,
110 smith 89, 90, 92,

Mahesh

SQLCM
Starting Member

13 Posts

Posted - 2007-06-06 : 13:11:09

Khtan, my concern is that it is almost impossible to manually select each row and union them one by one. Is there a better way to do this?

declare @table table
(
employee_ID int,
last_name varchar(10),
test_date datetime,
test_score int
)
insert into @table
select 34, 'Taylor', '02/05/2006', 85 union all
select 85, 'Lomeli', '03/15/2006', 72 union all
select 85, 'Lomeli', '03/25/2006', 78 union all
select 110, 'smith', '03/17/2006', 90 union all
select 110, 'smith', '03/24/2006', 89 union all
select 110, 'smith', '05/05/2006', 92

SQLCM
Starting Member

13 Posts

Posted - 2007-06-06 : 13:16:39

mahesh, is it possible to have each test score in seperate columns? (first test score in column 1, second test score in column 2, third test score in column 3)

Thanks.

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2007-06-06 : 14:11:51

Why you try to make a big turn to get your result?

SQLCM
Starting Member

13 Posts

Posted - 2007-06-06 : 14:16:30

My end user is very specific about the format of the report.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-06 : 15:17:46

quote:
Originally posted by SQLCM

Khtan, my concern is that it is almost impossible to manually select each row and union them one by one. Is there a better way to do this?

declare @table table
(
employee_ID int,
last_name varchar(10),
test_date datetime,
test_score int
)
insert into @table
select 34, 'Taylor', '02/05/2006', 85 union all
select 85, 'Lomeli', '03/15/2006', 72 union all
select 85, 'Lomeli', '03/25/2006', 78 union all
select 110, 'smith', '03/17/2006', 90 union all
select 110, 'smith', '03/24/2006', 89 union all
select 110, 'smith', '05/05/2006', 92

What do you mean? He was just providing some sample data for you so that you can see how it works! You take his code and apply it to your own situation. The INSERT statements and the table variable are just examples!

- Jeff
http://weblogs.sqlteam.com/JeffS

SQLCM
Starting Member

13 Posts

Posted - 2007-06-06 : 17:19:25

Thank you everyone for sharing your knowledge. I was able to get the result I was looking for.

SQLCM
Starting Member

13 Posts

Posted - 2007-06-06 : 19:28:07

Okay, I thought I understood how Khtan's query works but I couldn't get the correct number for test_score_3

What will the query look like if I would like to select 3 scores instead of 2?
select employee_ID, last_name,
test_score_1 = (select max(test_score) from @table x where x.employee_ID = t.employee_ID),
test_score_2 = (select max(test_score) from @table x where x.employee_ID = t.employee_ID
and x.test_score <> (select max(test_score) from @table y where y.employee_ID = x.employee_ID))
test_score_3 = ?
from @table t
group by employee_ID, last_name

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-06 : 20:07:08

You said you only wanted the top 2, so that's what he gave you. If you want the top 3, or more, or something else entirely, you have to tell us -- otherwise, it is just wasting our time.

- Jeff
http://weblogs.sqlteam.com/JeffS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-06 : 21:38:26

seriously this will be easier and faster if you were perform the pivoting in your front end reporting tool


declare @table table
(
	employee_ID 	int,
	last_name 	varchar(10),
	test_date 	datetime,
	test_score	int
)
insert into @table
select	 34, 'Taylor', '02/05/2006', 85 union all
select	 85, 'Lomeli', '03/15/2006', 72 union all
select	 85, 'Lomeli', '03/25/2006', 78 union all
select	110, 'smith',  '03/17/2006', 90 union all
select	110, 'smith',  '03/24/2006', 89 union all
select	110, 'smith',  '05/05/2006', 92

select	employee_ID, last_name,
	test_score_1	= max(case when rank = 1 then test_score end),
	test_score_2	= max(case when rank = 2 then test_score end),
	test_score_3	= max(case when rank = 3 then test_score end),
	test_score_4	= max(case when rank = 4 then test_score end)
from
(
	select	employee_ID, last_name, test_score,
		rank = (select count(*) from @table x where x.employee_ID = t.employee_ID and x.test_score >= t.test_score)
	from	@table t
) d
group by employee_ID, last_name

/*
employee_ID last_name  test_score_1 test_score_2 test_score_3 test_score_4 
----------- ---------- ------------ ------------ ------------ ------------ 
         85 Lomeli               78           72 NULL         NULL
         34 Taylor               85 NULL         NULL         NULL
        110 smith                92           90           89 NULL
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources