row value to column header value

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

General SQL Server Forums

New to SQL Server Programming

row value to column header value

Author

Topic

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 01:12:55

Hi,

From the below query i need to display the assignment name as column header.
for the assignment column i need to display the pointsawarded as row value.

select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'
and cs.courserole=2

output should be

firstname lastname <assignmentname1> <assignmentname2>
xx yy 67 56

Note: above query has 5 assignments, the column header should contains 5 assignemntname.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 01:31:21

[code]
select *
from
(
select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'
and cs.courserole=2
)m
PIVOT(max(pointsawarded) FOR assignmentname in ([Name1],[Name2],[Name3],..))p
[/code]

you need to give actual assignment names values instead of Name1,Name2 etc in above code

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 01:37:44

i didnot know the actual values i may differ. i should get from a.assignmentname from the above query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 02:04:26

[code]
DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT ',[' + assignmentname + ']' FROM assignment FOR XML PATH('')),1,1,'')

SET @Sql ='select
from
(
select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(pointsawarded) FOR assignmentname IN(' + @AssignmentList + '))p'
[/code]

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-01 : 03:01:47

quote:
Originally posted by shriroshanraj

i didnot know the actual values i may differ. i should get from a.assignmentname from the above query.

You can write dynamic pivot query by looking into the following URL
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Try to get the knowledge on which concepts you doesn't know...

--
Chandu

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 05:08:10

visak,

when i run the query by exec(@sql). i am getting this error

Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 't'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 05:22:37

quote:
Originally posted by shriroshanraj

when i run the query by exec(@sql). i am getting this error

Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 't'.

you need to execute it

use


EXEC(@SQL)

for that

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 05:24:58

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 't'.

i am getting this error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 05:25:24

quote:
Originally posted by shriroshanraj

visak,

when i run the query by exec(@sql). i am getting this error

Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 't'.

Missed a *


DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT ',[' + assignmentname + ']' FROM assignment FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid 
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(pointsawarded) FOR assignmentname IN(' + @AssignmentList + '))p'

EXEC (@SQL)

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 05:37:54

Now i am getting this error

Msg 8156, Level 16, State 1, Line 15
The column 'Create report on vendor comparisons' was specified multiple times for 'p'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 06:06:19

that means you've duplicate values in assignment table


DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT DISTINCT ',[' + assignmentname + ']' FROM assignment FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid 
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(pointsawarded) FOR assignmentname IN(' + @AssignmentList + '))p'

EXEC (@SQL)

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 06:10:11

visak,

No duplicate values in the table.

It displays all the assignmentname in the table. it should display according to the coursesectionid. i added the condition for assignment name.

pointsawarded should display under the assignmentname column header.
But it is not displaying.

DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT DISTINCT ',[' + assignmentname + ']' FROM assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(pointsawarded) FOR assignmentname IN(' + @AssignmentList + '))p'

EXEC (@SQL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 06:17:01

quote:
Originally posted by shriroshanraj

visak,

No duplicate values in the table.

Then you wont get the error you posted earlier
Check assignment table and see if you've multiple instances of value 'Create report on vendor comparisons'

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 06:23:50

pointsawarded should display under the assignmentname column header.
But it is not displaying.

DECLARE @AssignmentList varchar(3000),@Sql varchar(max)
SET @AssignmentList =STUFF((SELECT DISTINCT ',[' + assignmentname + ']' FROM assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' FOR XML PATH('')),1,1,'')

SET @Sql ='select *
from
(
select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid=''b78a6efe-ac77-4e49-806a-fc2fad71068b''
and cs.courserole=2
)t
PIVOT(SUM(pointsawarded) FOR assignmentname IN(' + @AssignmentList + '))p'

EXEC (@SQL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 06:29:46

then post what output you're getting currently using above query and what you actually want.

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-07-01 : 06:31:12

I need to display pointswarded/pointspossible from the above query. its slash not dividesymbol. how can i display under assignmentname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 06:33:47

quote:
Originally posted by shriroshanraj

i got it, thanks visakh

How did you solve it?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-01 : 06:57:20

quote:
Originally posted by shriroshanraj

I need to display pointswarded/pointspossible from the above query. its slash not dividesymbol. how can i display under assignmentname

is it a never ending riddle?
some minutes before you reported

quote:

Originally posted by shriroshanraj

i got it, thanks visakh

and then you edited it with above query.
ALso how do you've expect us to know from where you'll get pointspossible value? None of your posted queries so far had that column nor did you show us any output you would like to see.
Atleast give us a clue on where that field is supposed to come
and what you mean by pointswarded/pointspossible ? do you mean both columns contents coming inside same column in concatenated form?

------------------------------------------------------------------------------------------------------
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