SQL SP Row to Col + one more table . RSS

• • Reply
  

  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 

  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you
• • Reply
  

  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_Table2

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
• • Reply
  

  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

  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you
• • Reply
  

  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_Table2

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Marked as answer by ayyappan.cnn on Dec 17, 2012 03:46 PM
• • Reply
  

  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,

  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you
• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: SQL SP Row to Col + one more table .

  Dec 17, 2012 02:16 AM|LINK

  ayyappan.CNN

  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,

  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

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Dec 17, 2012 02:16 AM
  • Edited by sandeepmittal11 on Dec 17, 2012 02:18 AM
  • Marked as answer by ayyappan.cnn on Dec 17, 2012 03:46 PM
• • Reply
  

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

  
  

• • Reply
  

  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

  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you
• • Reply
  

  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_Table2

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Marked as answer by ayyappan.cnn on Dec 17, 2012 03:46 PM
• • Reply
  

  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, 

  Ayyappan.CNN,
  HAND IN HAND IND., Asst. Project Director, Admin and IT,Systems, TN,India
  
  Mark post(s) as "Answer" that helped you