How to separate value exist on temp table dynamically separated by comma ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  March 26, 2020 at 12:03 pm

  #3737137

  How to separate value exist on temp table dynamically separated by comma ?

  problem

  How to separate value exist on temp table dynamically separated by comma ?

  i work on sql server 2012 i need to separate field TeamName by comma

  teamname ,teamname+'Date'

  i need to loop to teamname filed in tmp table #Teams then separate by comma and every field will be next field + 'Date'

  really #temp table #teams every time changed so i dont have static data so i need any way dynamically sepatae field

  and second will be field+date

  CREATE TABLE #Teams
  (
  TeamId int,
  TeamName nvarchar(200)
  )
  insert into #Teams(TeamId,TeamName) 
  values
  (1,'Package'),
  (2,'Parametric'),
  (3,'Scribing'),
  (4,'Lifecycle')

   

  i need result as

  Package,PackageDate,Parametric,ParametricDate,Scribing,ScribingDate,Lifecycle,LifecycleDate

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  March 26, 2020 at 1:24 pm

  #3737169

  select string_agg(concat(TeamName, ',', concat(TeamName, 'Date')), ',') from #Teams;

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  March 26, 2020 at 1:26 pm

  #3737170

  O the question is for 2012.  Sorry, it needs the xml trick.  The above is 2017+

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• Steve Collins

  SSCrazy

  Points: 2068

  More actions

  March 26, 2020 at 1:55 pm

  #3737181

  select stuff((select concat(' ', TeamName, ',', concat(TeamName, 'Date')) 
  from #Teams for xml path ('')), 1, 1, '');
  

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können