SQL currently using pivot but not sure about it

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)

SQL currently using pivot but not sure about it

Author

Topic

jaycee
Starting Member

18 Posts

Posted - 2013-07-17 : 16:14:08

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

52326 Posts

Posted - 2013-07-18 : 01:18:02

[code]
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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jaycee
Starting Member

18 Posts

Posted - 2013-07-18 : 02:14:14

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:15:42

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

jaycee
Starting Member

18 Posts

Posted - 2013-07-18 : 03:16:53

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 04:17:12

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

jaycee
Starting Member

18 Posts

Posted - 2013-07-18 : 07:17:43

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 07:19:42

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources