Need help with a PIVOT

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  August 4, 2021 at 4:58 pm

  #3916923

  I have the following query

   

  SELECT DISTINCT E.[Widget_ID],l.[Widget_Name]

  FROM LandingTable l

  LEFT JOIN PROD_Table E ON l.[Widget_Name] = E.[Widget_Name]

  WHERE E.[Widget_ID] IS NULL

  My results are

  Widget_ID             Widget_Name

  NULL                       Widget_1

  NULL                       Widget_2

   

  How can I pivot the Widget_Name column so my new result set is as follows

  Widget_Names

  Widget_1, Widget_2

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  August 5, 2021 at 5:10 pm

  #3917236

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Jeffrey Williams

  SSC Guru

  Points: 90342

  More actions

  August 5, 2021 at 9:56 pm

  #3917288

  For 2016 and below - you can use FOR XML, for 2017 and above you can use STRING_AGG.

  Declare @testData Table (Widget_ID int, Widget_Name varchar(10));
   Insert Into @testData (Widget_ID, Widget_Name)
   Values (Null, 'Widget_1')
        , (Null, 'Widget_2');
  
   Select *
        , Widget_Names = stuff((Select concat(',', td2.Widget_Name)
                                  From @testData td2
                                 Where td2.Widget_ID Is Null
                                 Order By
                                       td2.Widget_ID
                                   For xml Path(''), Type).value('.', 'varchar(8000)'), 1, 1, '')
     From @testData      
  

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs