Help with query

• peter larsen-490879

  SSC-Addicted

  Points: 437

  More actions

  January 3, 2022 at 10:28 am

  #3969257

  Hi  all,

  This code gives 2 results

  SELECT distinct(AtcCode) as id, Atc_Text
  FROM [dbo].[Drugs_BasicData] d 
  inner join [dbo].[ATC_CodeTexts] t on t.AtcCode = d.ATC AND t.County = 'DK'
  INNER JOIN dbo.Companies c on d.Distributor = c.CompanyID 
  WHERE c.Country = 'DK' AND LEN(AtcCode) > 5 
  and Atc_Text like 'Cefalexin%'
  Order by Atc_Text;

   

  id   Atc_Text
  QJ01DB01   Cefalexin                                         
  QJ51DB01   Cefalexin

  But i need it as

   

  id                Atc_Text
  QJ01DB01,QJ51DB01Cefalexin

  Can anyone help with this?

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  January 3, 2022 at 11:29 am

  #3969266

  Use STRING_AGG(). This might work (difficult to test without DDL and sample data):

  SELECT distinctid = STRING_AGG(AtcCode, ',')
        ,Atc_Text
  FROM dbo.Drugs_BasicData d
      INNER JOIN dbo.ATC_CodeTexts t
          ON t.AtcCode = d.ATC
             AND t.County = 'DK'
      INNER JOIN dbo.Companies c
          ON d.Distributor = c.CompanyID
  WHERE c.Country = 'DK'
        AND LEN(AtcCode) > 5
        AND Atc_Text LIKE 'Cefalexin%'
  GROUP BY Atc_Text
  ORDER BY Atc_Text;

  • This reply was modified 2 years, 10 months ago by  Phil Parkin.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin