January 27, 2014 at 7:29 am
I have 2 tables People and Scores. A person might have 1-5 scores (unknown at time of Query). I would like to query the two tables into a results table and if person does not have a record the score will be zero. Scores also have a test number so you know which score it is. I can get it done with Stored Proc but I have to use Temp tables and then put the temp tables together. My skill level does not allow me to do it any other way but I am sure their is a simpler way. TIA. Sorry columns did not line up but I did not know how to do that. Pretty new to all of this.
People
Name ID
Tom5
Dick2
Harry3
Larry4
Curly1
Scores
PrimaryKeyPeopleIDScoreTestNumber
12801
25901
33901
44501
511001
61803
71902
83902
94702
101905
114903
1231003
135702
144904
151704
Results
PrimaryKeyPeopleIdScore1Score2Score3Score4Score5Name
1110090807090Curly
22800000Dick
33909010000Harry
44507090900Larry
559070000Tom
January 27, 2014 at 7:56 am
Hi and welcome to the forums. What you are describing is known as a cross tab or pivot. There are 2 articles referenced in my signature that explain how to do this. The first is a "static" cross tab. You can use this when you know what the maximum number of columns is going to be. If you have a variable number of columns you will need to use a dynamic cross tab. This is a bit more complicated but still totally manageable.
Take a look at those articles and post back if you need more help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2014 at 8:11 am
Solution for your problem may be something like that:
SELECT
PeopleID
,Name
,COALESCE([1],0) AS Score1
,COALESCE([2],0) AS Score2
,COALESCE([3],0) AS Score3
,COALESCE([4],0) AS Score4
,COALESCE([5],0) AS Score5
FROM
(
SELECT
sc.PeopleID
,SUM(sc.Score) AS Score
,sc.TestNumber
,pe.Name
FROM [dbo].[Scores] sc
LEFT JOIN
dbo.People pe
ON
pe.ID = sc.PeopleID
GROUP BY
sc.PeopleID
,sc.TestNumber
,pe.Name
) p
PIVOT
(
SUM(Score)
FOR TestNumber IN ([1],[2],[3],[4],[5])
) AS pvt
January 27, 2014 at 8:15 am
krunoslav-zibreg (1/27/2014)
Solution for your problem may be something like that:
PIVOT will work but I find that cross tabs are easier to read and understand. They also have the benefit of almost always winning the performance race.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2014 at 8:34 am
Since you are brand new around here you probably don't know that we like to see ddl and sample data in a consumable format. Something like this.
if OBJECT_ID('tempdb..#People') is not null
drop table #People
create table #People
(
Name varchar(10),
ID int
)
if OBJECT_ID('tempdb..#Scores') is not null
drop table #Scores
create table #Scores
(
PrimaryKey int,
PeopleID int,
Score int,
TestNumber int
)
insert #People
select 'Tom', 5 union all
select 'Dick', 2 union all
select 'Harry', 3 union all
select 'Larry', 4 union all
select 'Curly', 1
insert #Scores
select 1, 2, 80, 1 union all
select 2, 5, 90, 1 union all
select 3, 3, 90, 1 union all
select 4, 4, 50, 1 union all
select 5, 1, 100, 1 union all
select 6, 1, 80, 3 union all
select 7, 1, 90, 2 union all
select 8, 3, 90, 2 union all
select 9, 4, 70, 2 union all
select 10, 1, 90, 5 union all
select 11, 4, 90, 3 union all
select 12, 3, 100, 3 union all
select 13, 5, 70, 2 union all
select 14, 4, 90, 4 union all
select 15, 1, 70, 4
This makes is very simple for us to work on your issue.
Below are two ways to attack this problem. The first is the cross tab style I was describing. The second is the PIVOT query posted by krunoslav-zibreg (changed to use the same temp tables).
--CROSSTAB
select
s.PeopleID,
MAX(case when TestNumber = 1 then Score else 0 end) as Score1,
MAX(case when TestNumber = 2 then Score else 0 end) as Score2,
MAX(case when TestNumber = 3 then Score else 0 end) as Score3,
MAX(case when TestNumber = 4 then Score else 0 end) as Score4,
MAX(case when TestNumber = 5 then Score else 0 end) as Score5,
p.Name
from #People p
join #Scores s on p.ID = s.PeopleID
group by Name, s.PeopleID
order by Name, s.PeopleID
--PIVOT (krunoslav-zibreg)
SELECT
PeopleID,
Name,
COALESCE([1],0) AS Score1,
COALESCE([2],0) AS Score2,
COALESCE([3],0) AS Score3,
COALESCE([4],0) AS Score4,
COALESCE([5],0) AS Score5
FROM
(
SELECT
sc.PeopleID,
SUM(sc.Score) AS Score,
sc.TestNumber,
pe.Name
FROM #Scores sc
LEFT JOIN #People pe ON pe.ID = sc.PeopleID
GROUP BY sc.PeopleID, sc.TestNumber, pe.Name
) p
PIVOT
(
SUM(Score)
FOR TestNumber IN ([1],[2],[3],[4],[5])
) AS pvt
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2014 at 8:35 am
Thank both of you very much for your quick response. I will read the article and I will also try the code. If I have issues I will be back. Thanks again.
January 27, 2014 at 8:44 am
I understand. I'm sorry but I just was not aware of how this all works. I will get better. Thanks again.
January 27, 2014 at 11:42 am
Wow what a great way to do this. I am reading a great deal about pivots and cross tabs. I can see their huge power but with my skill level I am not sure I could have accomplished what you guys have shown me. SSC Veteran and Sean Thank You So Much. This forum has a great following.
January 27, 2014 at 11:54 am
Terry Bottorff (1/27/2014)
Wow what a great way to do this. I am reading a great deal about pivots and cross tabs. I can see their huge power but with my skill level I am not sure I could have accomplished what you guys have shown me. SSC Veteran and Sean Thank You So Much. This forum has a great following.
You are quite welcome. I am glad that you are able to take those concepts and use them in your own situation. These forums have a ton of information but sometimes it is kind of like drinking from a firehose. Come back and visit us again when you have issues on another topic or need some help with this one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply