January 7, 2013 at 9:49 am
Hi Friends,
I am having data structure like:
create table tbl_student
(studentid int identity(1,1),
stuname varchar(50),
facultyid int,
facultyraiting int,
topicidint,
date_time datetime)
insert into tbl_student select 'Shubham',1,5,2,getdate()union select'Ram Kumar',1,6,2,getdate()
union select'Deepak',1,6,2,getdate() union select'Kiran',1,6,2,getdate()
union
select 'Pramod',3,5,2,getdate()union select'Rajeev Kumar',3,6,2,getdate()
union select'Ramesh',2,6,3,getdate() union select'Vijay',2,6,3,getdate()
select * from tbl_student
create table topicMaster
(topicID int identity(1,1),
topicName varchar(50)
)
create table facultymaster
(facultyid int identity(1,1) ,
facultyname varchar(50))
insert into facultymaster select 'Faculty1'union select 'faculty2' union select 'faculty3'
insert into topicmaster select 'topic1' union select 'topic2' union select 'topic3'
now i want to display record like:
StudentsFaculty1/Topic1/Date1Faculty2/Topic1/Date1Faculty3/Topic1/Date1
RatingMarksRatingMarks RatingMarks
Deepak 6 null null
Kiran 6 null null
Pramod null null null
Rajeev Kumar null null null
Ram Kumar 6 null null
Ramesh null 6 null
Shubham 5 null null
Vijay null 6 null
please suggest how can accomplish it in best possible manner
January 7, 2013 at 12:08 pm
Thanks for posting ddl and desired output. The following mostly works for you example but it includes Rating3 which in your sample output it was missing.
select stuname,
case when s.facultyid = 1 then facultyraiting else null end as Rating1,
case when s.facultyid = 2 then facultyraiting else null end as Rating2,
case when s.facultyid = 3 then facultyraiting else null end as Rating3
from topicmaster tm
left join tbl_student s on tm.topicID = s.topicid
left join facultyMaster fm on fm.facultyid = s.facultyid
where studentid is not null
_______________________________________________________________
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply