How to write query for multiple rows into a single row for different columns

Question

Post reply

How to write query for multiple rows into a single row for different columns

Harry

Old Hand

Points: 337
More actions
September 10, 2020 at 6:04 pm
Go to Answer
#3787659
How to write query for multiple rows into a single row for different columns in SQL Server
While storing in table, I am inserting like below.
```
CountryIDCityIDTownID
10611
10612
10311
10312
10313
11911
11912
111411
111412
```
But when i want to write query, i just need to query like below. (Result)
```
CountryIDCityIDTownID
10611,12
10311,12,13
119,1411,12
```
I have used STUFF() function for 2 columns, but in above case i am not able to achieve. Please help me on this.
Thanks!
- This topic was modified 4 years, 2 months ago by Harry.
September 11, 2020 at 5:33 am

This was removed by the editor as SPAM
September 11, 2020 at 1:58 pm

This was removed by the editor as SPAM

Jeffrey Williams

SSC Guru

Points: 90303

September 11, 2020 at 4:33 pm

Answer

#3787951

Taking Jeff's sample data and example using STRING_AGG:

   With ctePreAgg 
     As (
 Select CountryID
      , CityID
      --, TownID = STRING_AGG(TownID,',') WITHIN GROUP (ORDER BY TownID)
      , TownID = stuff((Select ',' + cast(tt1.TownID As varchar)
                          From #TestTable       tt1
                         Where tt.CityID = tt1.CityID
                           And tt.CountryID = tt1.CountryID
                         Order By tt1.TownID
                           For xml Path('')),1,1,'')
   From #TestTable              tt
  Group By 
        CountryID
      , CityID
        )
 Select CountryID
      --, CityID = STRING_AGG(CityID,',') WITHIN GROUP (ORDER BY CityID), TownID
      , CityID = stuff((Select ',' + cast(ct1.CityID As varchar)
                          From ctePreAgg        ct1
                         Where ct1.TownID = ct.TownID
                           And ct1.CountryID  = ct.CountryID
                         Order By ct1.CityID
                           For xml Path('')),1,1,'')
      , TownID
   From ctePreAgg               ct
  Group By 
        CountryID
      , TownID
  Order By 
        CountryID
      , TownID;

I removed the DISTINCT because we need the ORDER BY to make sure the entries in each grouping are sorted. If there can be duplicates then you need to remove those duplicates before using the XML concatenation.

This reply was modified 4 years, 2 months ago by Jeffrey Williams.

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

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Look at the function STRING_AGG which should do exactly what you are needing.

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

First, if you get into the habit of posting "readily consumable data", you'll stand a much better chance of getting a coded answer and getting it a lot quicker. Here's one way you could have posted your data as "readily consumable data".

 SELECT *
   INTO #TestTable
   FROM  (VALUES
         (10,6 ,11)
        ,(10,6 ,12)
        ,(10,3 ,11)
        ,(10,3 ,12)
        ,(10,3 ,13)
        ,(11,9 ,11)
        ,(11,9 ,12)
        ,(11,14,11)
        ,(11,14,12)
        )v(CountryID,CityID,TownID)
;

See the article at the first link in my signature line below for more information on that subject.

For the given data and what appears to be a required sort order (CountryID followed by the CSV aggregation for TownID), the following code does the trick.

   WITH ctePreAgg AS
(
 SELECT CountryID, CityID, TownID = STRING_AGG(TownID,',') WITHIN GROUP (ORDER BY TownID)
   FROM #TestTable
  GROUP BY CountryID, CityID
)
  SELECT CountryID, CityID = STRING_AGG(CityID,',') WITHIN GROUP (ORDER BY CityID), TownID
   FROM ctePreAgg
  GROUP BY CountryID, TownID
  ORDER BY CountryID, TownID
;

That produces the desired output of...

--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)

Harry Old Hand Points: 337 More actions · Answer 3

Hi @jeff Moden,

Thank you information, I will follow that. I am newbie here.

I am having SQL Server 2014. So that, STRING_AGG function is not working. I have posted wrongly in 2019 forum.

Again, I have tried with STUFF() as below and I unable to get my expected output. Please suggest me on this.

IF OBJECT_ID(N'tempdb..#TempCountry') IS NOT NULL
BEGIN
       DROP TABLE #TempCountry
END
    
SELECT  
    TT.CountryID,
    STUFF((  
    SELECT  DISTINCT ',' + CAST(TT1.CityID AS VARCHAR)
    FROM #TestTable TT1 WHERE TT.CountryID = TT1.CountryID           
    FOR XML PATH ('')),1,1,'') AS CityID,
    STUFF((  
    SELECT  DISTINCT ',' + CAST(TT1.TownID AS VARCHAR)
    FROM #TestTable TT1 WHERE TT.CountryID = TT1.CountryID           
    FOR XML PATH ('')),1,1,'') AS TownID
    INTO #TempCountry
FROM
#TestTable TT
GROUP BY TT.CountryID

SELECT DISTINCT CountryID,CityID,TownID FROM #TempCountry

Thanks.

Harry Old Hand Points: 337 More actions · Answer 4

Hi @jcelko212 32090

This is my first post and sorry for that I dint posted DDL query. I am using the constraints while creating tables. This is the sample data which I posted for representation (this is not my actual table format). I am afraid of your comments.

Thanks.

This reply was modified 4 years, 2 months ago by Harry.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

That should do it, Mr. Williams.

--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)

Harry Old Hand Points: 337 More actions · Answer 6

Hi @jeff Moden, @Jeffrey Williams,

Is there any way to achieve this through STUFF() function. Since I am having SQL server 2014.

Thanks

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

Harry wrote:

Hi @jeff Moden, @Jeffrey Williams,
Is there any way to achieve this through STUFF() function. Since I am having SQL server 2014.
Thanks

Did you see what I posted?

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

Harry Old Hand Points: 337 More actions · Answer 8

Thanks Jeffrey Williams and Jeff Moden.

The query works for my data.