help with SQL Query

• denis.gendera

  Right there with Babe

  Points: 733

  More actions

  January 30, 2017 at 8:45 am

  #326468

  I've got data shown in following format

  regioncountry     sserver
  eu-belbru              ab1
  eu-belbru              ab2
  eu-belbru              ab3
  eu-belbru              ab4
  eu-belfra              ab10

  I would like to show data following way
  regioncountry      sserver
  eu-belbru             ab1;ab2;ab3;ab4
  eu-belfra              ab10

  CREATE table #TempTable
  (
    regioncountry varchar(50),
    sserver Varchar(50)
  )
  insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab1')
  insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab2')
  insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab3')
  insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab4')
  insert into #TempTable (regioncountry,sserver) values ('eu-belfra','ab10')

  select * from #TempTable

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 30, 2017 at 8:50 am

  #1925615

  This should help.

  John

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  January 30, 2017 at 9:56 am

  #1925644

  Almost the same as John pointed out but performs better
  😎

  
CREATE table #TempTable
(
regioncountry varchar(50),
sserver Varchar(50)
)
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab1')
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab2')
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab3')
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab4')
insert into #TempTable (regioncountry,sserver) values ('eu-belfra','ab10')

  SELECT
  T.regioncountry
 ,(STUFF((SELECT
   CHAR(44) + ST.sserver
  FROM #TempTable  ST
  WHERE ST.regioncountry = T.regioncountry
  FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(100)'),1,1,'')) AS sserver
FROM #TempTable T
GROUP BY T.regioncountry;
DROP TABLE #TempTable;


  Output
  regioncountry   sserver
--------------- ----------------
eu-belbru       ab1,ab2,ab3,ab4
eu-belfra       ab10o


• denis.gendera

  Right there with Babe

  Points: 733

  More actions

  January 30, 2017 at 10:00 am

  #1925646

  Eirikur Eiriksson - Monday, January 30, 2017 9:56 AM

  Almost the same as John pointed out but performs better
  😎

  
CREATE table #TempTable
(
regioncountry varchar(50),
sserver Varchar(50)
)
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab1')
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab2')
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab3')
insert into #TempTable (regioncountry,sserver) values ('eu-belbru','ab4')
insert into #TempTable (regioncountry,sserver) values ('eu-belfra','ab10')

  SELECT
  T.regioncountry
 ,(STUFF((SELECT
   CHAR(44) + ST.sserver
  FROM #TempTable  ST
  WHERE ST.regioncountry = T.regioncountry
  FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(100)'),1,1,'')) AS sserver
FROM #TempTable T
GROUP BY T.regioncountry;
DROP TABLE #TempTable;


  Output
  regioncountry   sserver
--------------- ----------------
eu-belbru       ab1,ab2,ab3,ab4
eu-belfra       ab10o


  thank you all for the quick replies much appreciated.