MSSQL 2008 R2: select one repeating column once, and the remaining columns are separated by commas

Question

I am using mssql 2008 R2

have a structure

create table #Profile (pro_id int,surname varchar(30),firstname varchar(30)) insert #Profile select 1,'John', 'James' create table #Qualification (pro_id int,Degree varchar(30),School varchar(30),Year int) insert #Qualification select 1 ,'LLB' ,'Yale University' , 2001 union select 1, 'MBA', 'Wharton university', 2002 create table #Projects (pro_id int,Title varchar(30),Year int) insert #Projects select 1 , 'Excavation of aquatic debris', 2007 union select 1 , 'Social Networking', 2003 union select 1 , 'Excavation of aquatic debris', 2007 

I need a conclusion below

  1 John James MBA Wharton university 2002, LLB Yale University 2001 Social Networking 2003, Excavation of aquatic debris 2007, 

can get all the data stuck in comma separated o / p

  select p.pro_id,p.firstname,p.surname,--q.*,pr.* q.Degree +' '+ q.School ,q.Year , pr.Title,pr.Year from #Profile p inner join #Qualification q on p.pro_id = q.pro_id inner join #Projects pr on p.pro_id = pr.pro_id 

Any pointers to achieve this

Answer 1

Try this option -

Query:

 SELECT DISTINCT pro_id , surname + ' ' + firstname + STUFF(( SELECT ', ' + txt + ' ' + CAST(YEAR AS CHAR(4)) FROM ( SELECT id = 1, pro_id, txt = Degree + ' ' + School, [year] FROM #Qualification UNION ALL SELECT id = 2, pro_id, txt = Title, [year] FROM #Projects ) t2 WHERE t.pro_id = t2.pro_id ORDER BY id, t2.[Year] DESC FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') FROM #Profile t 

Output:

 ----------- ---------------------------------------------------------------------------------------------------------------------------- 1 John James MBA Wharton university 2002, LLB Yale University 2001, Excavation of aquatic debris 2007, Social Networking 2003