sql-server - MSSQL 2008 R2 : Selecting one duplicate columns once and rest of the columns comma separated

Question

我使用的是mssql 2008 R2

具有以下结构

   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 

我想要如下输出

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

能够获取所有数据，卡在逗号分隔的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

任何实现此目的的指针

Answer 1

试试这个 -

查询:

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

输出:

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