SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL currently using pivot but not sure about it
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 07/17/2013 :  16:14:08  Show Profile  Reply with Quote
Hi, If anyone has time to help, it is much appreciated. I have a table of applicant id's, qualifications (which keeps growing) and grades. I am trying to pivot the table so that I get the student id, the qualifications as a field name with the grade underneath it. The table:

create table appqual (aq_id int,applicant_id int,qualtitle varchar(max), grade varchar(max));

insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (184,322,'Statistics','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (186,322,'Mathematics','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (187,322,'Biology','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (188,322,'Chemistry','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (189,322,'Physics','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (191,322,'English Literature','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (192,322,'English Language','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (193,322,'Business Studies','A*')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (194,322,'French','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (195,322,'German','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (196,322,'Engineering','A*')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (199,324,'Chemistry','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (200,324,'Geology','D')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (201,324,'Biology','E')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (204,324,'Additional Science','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (206,324,'Geography','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (207,324,'Geography','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (209,324,'English Language','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (210,324,'Science A','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (211,324,'Mathematics B','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (212,324,'Religious Studies A','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (213,324,'Music','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (214,324,'Physical Education','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (215,324,'English Literature','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23145,-3863,'English Language','D')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23146,-3863,'Mathematics','E')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23147,-3863,'Science','E')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23867,-3729,'English Language','CC')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23868,-3729,'Mathematics','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23869,-3729,'Science','CD')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23870,-3729,'Applied Business','BB')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (23871,-3729,'Physical Education','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24002,-3704,'English Language','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24003,-3704,'English Literature','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24004,-3704,'Mathematics','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24005,-3704,'Geography','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24006,-3704,'Chemistry','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24007,-3704,'Biology','A*')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24008,-3704,'Physics','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24009,-3704,'Design and Technology: Food Technology','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24010,-3704,'Physical Education','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24866,-3547,'English Language','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24867,-3547,'English Literature','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24868,-3547,'Mathematics','D')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24869,-3547,'Science','D')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24870,-3547,'Information and Communication Technology','B,B,B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24871,-3547,'Engineering','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24872,-3547,'Design and Technology: Resistant Materials Technology','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24908,-3540,'English Language',' ')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24909,-3540,'Mathematics','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24910,-3540,'Science','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24911,-3540,'History',' ')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24912,-3540,'Engineering','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24913,-3540,'Statistics','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24932,-3536,'English Language',' ')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24933,-3536,'Mathematics',' ')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (24934,-3536,'Science',' ')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25065,-3511,'English Language','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25066,-3511,'English Literature','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25067,-3511,'Mathematics','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25068,-3511,'Science','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25069,-3511,'Spanish','B')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25070,-3511,'Chemistry','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25071,-3511,'Biology','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25072,-3511,'Physics','A')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (25073,-3511,'Physical Education','A')

--My idea being part way but creating duplicate applicant_id
--and with the Row_Num in the place of where I really want
--the grade to be

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(qualtitle)
FROM appqual
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT applicant_id, ' + @cols + ', grade from
(
SELECT DISTINCT applicant_id, qualtitle, grade, ROW_NUMBER() OVER (PARTITION BY applicant_id ORDER BY applicant_id) AS RowNum
FROM(
SELECT DISTINCT applicant_id, qualtitle, grade
FROM appqual
)t1
) x
pivot
(
min(RowNum)
for qualtitle in (' + @cols + ')
) p order by applicant_id asc'
execute(@query)
--Many thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/18/2013 :  01:18:02  Show Profile  Reply with Quote

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(qualtitle)
FROM appqual
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = 'SELECT applicant_id, ' + @cols + ', grade from 
(
SELECT applicant_id, qualtitle, grade,ROW_NUMBER() OVER (PARTITION BY applicant_id, qualtitle ORDER BY aq_id) AS Seq
FROM appqual
) x
pivot 
(
min(grade)
for qualtitle in (' + @cols + ')
) p 
order by applicant_id asc'
execute(@query)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 07/18/2013 :  02:14:14  Show Profile  Reply with Quote
Hi, Thanks.
When I run this, I get

Msg 207, Level 16, State 1, Line 1
Invalid column name 'grade'.

I tried to pivot round grade first of all but being a character field containing B, C Pass etc didn't think I could run an aggregate function on it.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/18/2013 :  02:15:42  Show Profile  Reply with Quote
quote:
Originally posted by jaycee

Hi, Thanks.
When I run this, I get

Msg 207, Level 16, State 1, Line 1
Invalid column name 'grade'.

I tried to pivot round grade first of all but being a character field containing B, C Pass etc didn't think I could run an aggregate function on it.



try



DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(qualtitle)
FROM appqual
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = 'SELECT applicant_id, ' + @cols + ', grade from 
(
SELECT applicant_id, qualtitle, grade,ROW_NUMBER() OVER (PARTITION BY applicant_id, qualtitle ORDER BY aq_id) AS Seq
FROM appqual
) x
pivot 
(
min(grade)
for qualtitle in (' + @cols + ')
) p 
order by applicant_id asc'
execute(@query)

 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 07/18/2013 :  03:16:53  Show Profile  Reply with Quote
Hi, that's got it running - but have you any idea why I am getting duplicate records out. In the short listing I have given you, applicant_id 324 is duplicated - with just a single grade c under geography. Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/18/2013 :  04:17:12  Show Profile  Reply with Quote
quote:
Originally posted by jaycee

Hi, that's got it running - but have you any idea why I am getting duplicate records out. In the short listing I have given you, applicant_id 324 is duplicated - with just a single grade c under geography. Thanks.


thats because you've two records in your source data for applicant_id 324 for geography ie below rows


..
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (206,324,'Geography','C')
insert into appqual (aq_id,applicant_id,qualtitle, grade) VALUES (207,324,'Geography','C')
..


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jaycee
Starting Member

United Kingdom
18 Posts

Posted - 07/18/2013 :  07:17:43  Show Profile  Reply with Quote
Must admit I hadn't noticed the duplicate record in there - it'll cause problems further down the line so will need addressing through the interface (back to development). That is great - many thanks for your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/18/2013 :  07:19:42  Show Profile  Reply with Quote
No problem. You're welcome
Handling it in source is certainly the best option if you dont want to consider the duplicates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000