create table Student_Table1 (Student_ID int, Student_Name varchar(100))
insert into Student_Table1
select 1, 'Johnson' union all
select 2, 'Murugan' union all
select 3, 'Shobana' union all
select 4, 'Kumar'
create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100))
insert into Marks_Table2
select 1, 'English' union all
select 2, 'Tamil' union all
select 3, 'Maths' union all
select 4, 'Social Science' union all
select 5, 'Science' union all
select 6, 'Sports'
select * into #Template from Marks_Table2
declare @collist varchar(max), @query nvarchar(max)
set @collist = (select stuff((select ',[' + cast(Student_ID as varchar) + ' - ' + Student_Name + '] varchar(100)'
from Student_Table1 for xml path('')),1,1,''))
set @query = 'alter table #Template add ' + @collist
exec (@query)
select * from #Template
drop table #Template
drop table Student_Table1
drop table Marks_Table2
create table Student_Table1 (Student_ID int, Student_Name varchar(100))
insert into Student_Table1
select 1, 'Johnson' union all
select 2, 'Murugan' union all
select 3, 'Shobana' union all
select 4, 'Kumar'
create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100))
insert into Marks_Table2
select 1, 'English' union all
select 2, 'Tamil' union all
select 3, 'Maths' union all
select 4, 'Social Science' union all
select 5, 'Science' union all
select 6, 'Sports'
declare @collist varchar(max), @query nvarchar(max)
set @collist = (select stuff((select ',[' + Student_Name + ']' from Student_Table1 for xml path('')),1,1,''))
set @query = 'select Subject_ID, Subject_Name, [Johnson],[Murugan],[Shobana],[Kumar]
from (
select *
from Student_Table1 cross apply Marks_Table2
) srv pivot (max(Student_ID) for Student_Name in([Johnson],[Murugan],[Shobana],[Kumar])) pvt'
exec (@query)
drop table Student_Table1
drop table Marks_Table2
Thanks, but we can't limit student names may comes 10 to 20... so am also trying .... pl send me little more examples....
Reagards,
I have posted the solution keeping in mind the same but mistakenly forgot to place @collist in the query. That's why i have used dynamic pivot, otherwise there was not need to use dynamic pivot. Use this
create table Student_Table1 (Student_ID int, Student_Name varchar(100))
insert into Student_Table1
select 1, 'Johnson' union all
select 2, 'Murugan' union all
select 3, 'Shobana' union all
select 4, 'Kumar'
create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100))
insert into Marks_Table2
select 1, 'English' union all
select 2, 'Tamil' union all
select 3, 'Maths' union all
select 4, 'Social Science' union all
select 5, 'Science' union all
select 6, 'Sports'
declare @collist varchar(max), @query nvarchar(max)
set @collist = (select stuff((select ',[' + Student_Name + ']' from Student_Table1 for xml path('')),1,1,''))
set @query = 'select Subject_ID, Subject_Name, ' + @collist
+' from (
select *
from Student_Table1 cross apply Marks_Table2
) srv pivot (max(Student_ID) for Student_Name in(' + @collist + ')) pvt'
exec (@query)
drop table Student_Table1
drop table Marks_Table2
Add same coloumn (Student_ID) in Marks_Table2 ,then Set forign key reference in Marks_Table2 from Student_ID and set values from student id(must be same values)
Suggest to always provide the entire requirement at once, as the solution may totally get changed as per the change in requriment. Also, always mark answer to the posts that worked for you as per your previous requirement and further post the changed requirment
solution as per your changed requirement
create table Student_Table1 (Student_ID int, Student_Name varchar(100))
insert into Student_Table1
select 1, 'Johnson' union all
select 2, 'Murugan' union all
select 3, 'Shobana' union all
select 4, 'Kumar'
create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100))
insert into Marks_Table2
select 1, 'English' union all
select 2, 'Tamil' union all
select 3, 'Maths' union all
select 4, 'Social Science' union all
select 5, 'Science' union all
select 6, 'Sports'
select Student_ID, Student_Name into #temp
from (
select CAST(Student_ID as varchar) as Student_ID, CAST(Student_ID as varchar) as Student_Name from Student_Table1
union all
select CAST(Student_ID as varchar) as Student_ID, Student_Name from Student_Table1
) tab order by Student_ID, Student_Name
declare @collist varchar(max), @query nvarchar(max)
set @collist = (select stuff((select ',[' + Student_Name + ']' from #temp order by Student_ID, Student_Name for xml path('')),1,1,''))
update #temp set Student_ID = '' where Student_ID<>Student_Name
set @query = 'select Subject_ID, Subject_Name, ' + @collist
+' from (
select *
from #temp cross apply Marks_Table2
) srv pivot (max(Student_ID) for Student_Name in(' + @collist + ')) pvt order by Subject_ID'
exec (@query)
drop table #temp
drop table Student_Table1
drop table Marks_Table2
ayyappan.CNN
Participant
870 Points
326 Posts
SQL SP Row to Col + one more table .
Dec 16, 2012 04:59 AM|LINK
Hi... how to do this ?
Student_Table1
Student_ID
Student_Name
1
Johnson
2
Murugan
3
Shobana
4
Kumar
Marks_Table2
Subject_ID
Subject_Name
1
English
2
Tamil
3
Maths
4
Social Science
5
Science
6
Sports
How to write SP &
I want to show following details and get marks information from ASPx Gridview (template)
Subject _ID
Subject_Name
1 - Johnson
2 - Murugan
3 - Shobana
4 - Kumar
1
English
2
Tamil
3
Maths
4
Social Science
5
Science
6
Sports
Thanks in advance
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: SQL SP Row to Col + one more table .
Dec 16, 2012 06:54 AM|LINK
create table Student_Table1 (Student_ID int, Student_Name varchar(100)) insert into Student_Table1 select 1, 'Johnson' union all select 2, 'Murugan' union all select 3, 'Shobana' union all select 4, 'Kumar' create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100)) insert into Marks_Table2 select 1, 'English' union all select 2, 'Tamil' union all select 3, 'Maths' union all select 4, 'Social Science' union all select 5, 'Science' union all select 6, 'Sports' select * into #Template from Marks_Table2 declare @collist varchar(max), @query nvarchar(max) set @collist = (select stuff((select ',[' + cast(Student_ID as varchar) + ' - ' + Student_Name + '] varchar(100)' from Student_Table1 for xml path('')),1,1,'')) set @query = 'alter table #Template add ' + @collist exec (@query) select * from #Template drop table #Template drop table Student_Table1 drop table Marks_Table2Sandeep Mittal | My Blog - IT Developer Zone
ayyappan.CNN
Participant
870 Points
326 Posts
Re: SQL SP Row to Col + one more table .
Dec 16, 2012 01:22 PM|LINK
Hi... thanks and one more simple thing, there is small change pls see following output model ... I want to show student ID in rows not in title..
Subject _ID
Subject_Name
Johnson
Murugan
Shobana
Kumar
1
English
1
2
3
4
2
Tamil
1
2
3
4
3
Maths
1
2
3
4
4
Social Science
1
2
3
4
5
Science
1
2
3
4
6
Sports
1
2
3
4
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: SQL SP Row to Col + one more table .
Dec 16, 2012 02:36 PM|LINK
Dynamic pivot will be required for this. you may refer this link to know more about dynamic pivot.
http://itdeveloperzone.blogspot.in/2011/11/dynamic-pivot-in-sql-server.html
Solution
create table Student_Table1 (Student_ID int, Student_Name varchar(100)) insert into Student_Table1 select 1, 'Johnson' union all select 2, 'Murugan' union all select 3, 'Shobana' union all select 4, 'Kumar' create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100)) insert into Marks_Table2 select 1, 'English' union all select 2, 'Tamil' union all select 3, 'Maths' union all select 4, 'Social Science' union all select 5, 'Science' union all select 6, 'Sports' declare @collist varchar(max), @query nvarchar(max) set @collist = (select stuff((select ',[' + Student_Name + ']' from Student_Table1 for xml path('')),1,1,'')) set @query = 'select Subject_ID, Subject_Name, [Johnson],[Murugan],[Shobana],[Kumar] from ( select * from Student_Table1 cross apply Marks_Table2 ) srv pivot (max(Student_ID) for Student_Name in([Johnson],[Murugan],[Shobana],[Kumar])) pvt' exec (@query) drop table Student_Table1 drop table Marks_Table2Sandeep Mittal | My Blog - IT Developer Zone
ayyappan.CNN
Participant
870 Points
326 Posts
Re: SQL SP Row to Col + one more table .
Dec 16, 2012 04:55 PM|LINK
Dear Mr.Sandeep,
Thanks, but we can't limit student names may comes 10 to 20... so am also trying .... pl send me little more examples....
Reagards,
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: SQL SP Row to Col + one more table .
Dec 17, 2012 02:16 AM|LINK
I have posted the solution keeping in mind the same but mistakenly forgot to place @collist in the query. That's why i have used dynamic pivot, otherwise there was not need to use dynamic pivot. Use this
create table Student_Table1 (Student_ID int, Student_Name varchar(100)) insert into Student_Table1 select 1, 'Johnson' union all select 2, 'Murugan' union all select 3, 'Shobana' union all select 4, 'Kumar' create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100)) insert into Marks_Table2 select 1, 'English' union all select 2, 'Tamil' union all select 3, 'Maths' union all select 4, 'Social Science' union all select 5, 'Science' union all select 6, 'Sports' declare @collist varchar(max), @query nvarchar(max) set @collist = (select stuff((select ',[' + Student_Name + ']' from Student_Table1 for xml path('')),1,1,'')) set @query = 'select Subject_ID, Subject_Name, ' + @collist +' from ( select * from Student_Table1 cross apply Marks_Table2 ) srv pivot (max(Student_ID) for Student_Name in(' + @collist + ')) pvt' exec (@query) drop table Student_Table1 drop table Marks_Table2Sandeep Mittal | My Blog - IT Developer Zone
RameshRajend...
Star
7983 Points
2099 Posts
Re: SQL SP Row to Col + one more table .
Dec 17, 2012 02:53 AM|LINK
Add same coloumn (Student_ID) in Marks_Table2 ,then Set forign key reference in Marks_Table2 from Student_ID and set values from student id(must be same values)
try this query
ALTER TABLE Orders
ADD CONSTRAINT Marks_Table2
FOREIGN KEY (Student_ID)
REFERENCES Student_Table1(Student_ID)
Then use innerjoin in two table for
select from Student_Table1 inner join Marks_Table2 on Marks_Table2.Student_ID=Student_Table1.Student_ID
Now this query was get all table values for forign key reference.
Then bind the values to gridview with u like a format way.....
ayyappan.CNN
Participant
870 Points
326 Posts
Re: SQL SP Row to Col + one more table .
Dec 17, 2012 05:44 AM|LINK
Hi… Sandeep,
it comes out well. (This is my final request)
But am asked Student ID & Name (separate columns) pl refer my previous threads,
ie.
Subject _ID
Subject_Name
Id
Johnson
Id
Murugan
Id
Shobana
Id
Kumar
1
English
1
2
3
4
2
Tamil
1
2
3
4
3
Maths
1
2
3
4
4
Social Science
1
2
3
4
5
Science
1
2
3
4
6
Sports
1
2
3
4
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: SQL SP Row to Col + one more table .
Dec 17, 2012 08:29 AM|LINK
Dear ayyappan.CNN
Suggest to always provide the entire requirement at once, as the solution may totally get changed as per the change in requriment. Also, always mark answer to the posts that worked for you as per your previous requirement and further post the changed requirment
solution as per your changed requirement
create table Student_Table1 (Student_ID int, Student_Name varchar(100)) insert into Student_Table1 select 1, 'Johnson' union all select 2, 'Murugan' union all select 3, 'Shobana' union all select 4, 'Kumar' create table Marks_Table2 (Subject_ID int, Subject_Name varchar(100)) insert into Marks_Table2 select 1, 'English' union all select 2, 'Tamil' union all select 3, 'Maths' union all select 4, 'Social Science' union all select 5, 'Science' union all select 6, 'Sports' select Student_ID, Student_Name into #temp from ( select CAST(Student_ID as varchar) as Student_ID, CAST(Student_ID as varchar) as Student_Name from Student_Table1 union all select CAST(Student_ID as varchar) as Student_ID, Student_Name from Student_Table1 ) tab order by Student_ID, Student_Name declare @collist varchar(max), @query nvarchar(max) set @collist = (select stuff((select ',[' + Student_Name + ']' from #temp order by Student_ID, Student_Name for xml path('')),1,1,'')) update #temp set Student_ID = '' where Student_ID<>Student_Name set @query = 'select Subject_ID, Subject_Name, ' + @collist +' from ( select * from #temp cross apply Marks_Table2 ) srv pivot (max(Student_ID) for Student_Name in(' + @collist + ')) pvt order by Subject_ID' exec (@query) drop table #temp drop table Student_Table1 drop table Marks_Table2Sandeep Mittal | My Blog - IT Developer Zone
ayyappan.CNN
Participant
870 Points
326 Posts
Re: SQL SP Row to Col + one more table .
Dec 17, 2012 03:47 PM|LINK
Yes, Thanks Mr.Sandeep.
Regards,
HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
Mark post(s) as "Answer" that helped you