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, Mar 18 '20 at 15:37
Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) — Thom A, Mar 18 '20 at 15:38

score 2 · Answer 1 · answered Mar 18 '20 at 15:39

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 '20 at 15:39

Tim Biegeleisen

502,043
27
286
360

I got an error that STRING_AGG is not recognized built-in function name. I am using SQL Server 2016 13.0.5102 (x64) – Moe Money Mar 18 '20 at 15:44
Time to upgrade `:-)` – Tim Biegeleisen Mar 18 '20 at 15:45
@MoeMoney The function was added in SQL Server 2017. Refer to Yogesh Sharma's answer for the very arcane alternative. – Bacon Bits Mar 18 '20 at 16:01

score 0 · Answer 2 · answered Mar 18 '20 at 15:40

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

Convert Rows to Column - SQL Server

2 Answers2