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
 General SQL Server Forums
 New to SQL Server Programming
 row value to column header value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  01:12:55  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/01/2013 :  01:31:21  Show Profile  Reply with Quote

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


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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  01:37:44  Show Profile  Reply with Quote
i didnot know the actual values i may differ. i should get from a.assignmentname from the above query.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

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

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'



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

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 07/01/2013 :  03:01:47  Show Profile  Reply with Quote
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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  05:08:10  Show Profile  Reply with Quote
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'.



Edited by - shriroshanraj on 07/01/2013 05:22:43
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  05:22:37  Show Profile  Reply with Quote
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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  05:24:58  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  05:25:24  Show Profile  Reply with Quote
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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  05:37:54  Show Profile  Reply with Quote
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'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  06:06:19  Show Profile  Reply with Quote
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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  06:10:11  Show Profile  Reply with Quote
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)



Edited by - shriroshanraj on 07/01/2013 06:20:27
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  06:17:01  Show Profile  Reply with Quote
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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  06:23:50  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  06:29:46  Show Profile  Reply with Quote
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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 07/01/2013 :  06:31:12  Show Profile  Reply with Quote
I need to display pointswarded/pointspossible from the above query. its slash not dividesymbol. how can i display under assignmentname

Edited by - shriroshanraj on 07/01/2013 06:39:15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  06:33:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/01/2013 :  06:57:20  Show Profile  Reply with Quote
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
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.11 seconds. Powered By: Snitz Forums 2000