Need help with a PIVOT

Question

Post reply

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

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: 90304 More actions · Answer 2

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