Convert Rows to Column - SQL Server

Question

I have a table with account numbers and products. the table looks like this:

ACCOUNT_NUMBER, Product
1234, 'Personal Loan'
1234, 'Saving Account'
1234, 'Auto Loan'
4321, 'Checking Account'
4321, 'Mortgage'

I would like to have a query to give the result in this format:

ACCOUNT_NUMBER, PRODUCTS
1234,'Personal Loan,Savinig Account, Auto Loan'
4321, 'Checking Account,Mortgage'

how would I achieve that? I tried XML PATH, but the performance was very bad and it throws error (server out of disk spacee)

" I tried XML PATH" Show us what you tried then, please. This sounds like you didn't use a correlated query. — Thom A
– Thom A ♦, Commented Mar 18, 2020 at 15:37
Does this answer your question? Comma separated results in SQL — Thom A
– Thom A ♦, Commented Mar 18, 2020 at 15:38

Tim Biegeleisen · Accepted Answer · 2020-03-18 15:39:53Z

2

It is 2020, so let's try to stay current and use the latest available STRING_AGG function in SQL Server:

SELECT
    ACCOUNT_NUMBER,
    STRING_AGG(Product, ',') WITHIN GROUP (ORDER BY Product) AS PRODUCTS
FROM yourTable
GROUP BY
    ACCOUNT_NUMBER
ORDER BY
    ACCOUNT_NUMBER;

There does not appear to be any rule for determining the order of products within the CSV list, so I am using the product name itself for that purpose. If you have other ordering logic, then state it and this answer can be modified.

answered Mar 18, 2020 at 15:39

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Moe Money Over a year ago

I got an error that STRING_AGG is not recognized built-in function name. I am using SQL Server 2016 13.0.5102 (x64)

Tim Biegeleisen Over a year ago

Time to upgrade :-)

Bacon Bits Over a year ago

@MoeMoney The function was added in SQL Server 2017. Refer to Yogesh Sharma's answer for the very arcane alternative.

Yogesh Sharma · Accepted Answer · 2020-03-18 15:40:23Z

0

You can use xml approach :

select t.account_number, 
       stuff( (select concat(', ', t1.Product)
               from table t1
               where t1.account_number = t.account_number
               for xml path('')
              ), 1, 1, ''
            ) as products
from (select distinct account_number
      from table t
     ) t;

For recent version, you can use STRING_AGG().

answered Mar 18, 2020 at 15:40

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Convert Rows to Column - SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related