combine values as pivot

Question

combine values as pivot

H2596310

SSC Enthusiast

Points: 172
More actions
October 24, 2022 at 6:08 am

#4103460

I have the following results after running a SQL. The first 2 columns are the result of the SQL.

The 3rd column is what I try to achieve. So combining all values where name is the same in a new column.
Is this possible using a pivot? or any other suggestion?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Without knowing how the underlying tables are structured, it's impossible to provide a coded answer. However, I suspect a combination of STRING_AGG() and GROUP BY should get you there. Some examples here.

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Maybe like this:

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData
(
    Name VARCHAR(50)
   ,Value VARCHAR(50)
);

INSERT #SomeData
(
    Name
   ,Value
)
VALUES
('A', 'X')
,('A', 'Y')
,('A', 'Z')
,('B', 'X')
,('B', 'Y');

SELECT sd.Name
      ,Value = STRING_AGG(sd.Value, ',')WITHIN GROUP(ORDER BY sd.Value)
FROM #SomeData sd
GROUP BY sd.Name;

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Reminder... STRING_AGG() wasn't available until 2017. The OP posted in a 2016 forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

First, welcome aboard. Just to help you on your postings, graphics are great for posting what you want to see or for additional explanations. Most folks on this forum like to test their code against your data to make sure that they're provided a correct result. Graphics of the input data don't help that cause.

With that, please see the article at the first link in my signature line below. Providing "Readily Consumable Data" will help us help you with better answers more quickly.

Here's an example of one way you could have posted the sample data for this particular post.

   DROP TABLE IF EXISTS #MyHead; --To make reruns easier
;
 SELECT v.Name, v.Value
   INTO #MyHead
   FROM (VALUES
         ('A','X')
        ,('A','Y')
        ,('A','Z')
        ,('B','X')
        ,('B','Y')
        ,('C','W')
        ,('C','X')
        ,('C','Y')
        ,('C','Z')
        )v(Name,Value)
;

Getting to the problem you posted, STRING_AGG() isn't available until SQL Server 2017. You've posted in a 2016 forum so I'm assuming that's what you're using.

The following will produce the required concatenation to create the CSVs by Name. If you need the Value column with the individual entries, as in your original post, you have to join the output of this code back to the original source to get it.

 SELECT  Name
        ,Combine  = STUFF(
                            (
                             SELECT N','+t2.Value
                               FROM #MyHead t2
                              WHERE t2.Name = t1.Name
                              ORDER BY t2.Value
                                FOR XML PATH(''), TYPE
                            ).value('./text()[1]','NVARCHAR(MAX)')
                    ,1,1,N'')
   FROM #MyHead t1
  GROUP BY Name
  ORDER BY Name
;

The XML PATH subquery does all the real work. The STUFF is used to remove the leading comma that the XML PATH can't help but make. Here's the output from that using the test data I provided.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Jeff Moden wrote:

Reminder... STRING_AGG() wasn't available until 2017. The OP posted in a 2016 forum.

Thanks, Jeff, I should have checked that!

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Phil Parkin wrote:

Jeff Moden wrote:
Reminder... STRING_AGG() wasn't available until 2017. The OP posted in a 2016 forum.
Thanks, Jeff, I should have checked that!

Not to worry... I've been bitten by that type of thing so often that it's (finally) one of the first things that I look at. The really good part about such "mistakes" is that they show how much simpler some code can be thanks to an upgrade. I have a deep hatred for the FOR XML PATH method, especially since the addition of TYPE to provide de-entitization of characters makes it run twice as slow but you almost dare not skip it because someone will use it in a place where it matters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)