CODEWITHSUNDEEP
sqlsql-server
n0rek
n0rek

Reputation: 90

How to combine values from multiple rows of a single column

I've got one table with bank accounts where're many copies of same number like showed:

SELECT
    k1.Account_number, COUNT(*) as Counter
FROM
    Customers k1
GROUP BY
    k1.Account_number
HAVING 
    COUNT(*) > 1
order by Counter

Gives:

Account_number                         Counter
DE1234567891234566786545                  3
FR8734280472398472394823                  4
IT48723849y3294723493284                  4
IT3543534534423421321323                  4
FR4798237492864239847329                  6
FR4723984793284723984732                  7

What I need is to put one more column with the number of customers that have got this account. I tried this:

STUFF((select '; ' + Customer_name from Customers FOR XML PATH('')),1,1,'') [Some_column]

But it puts all customers from that table :(

Upvotes: 0

Views: 86

Answers (2)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

It may work for you

SELECT
    k1.Account_number, COUNT(*) as Counter
    ,STUFF((SELECT ','+Customer_name FROM Customers K2 WHERE K2.Account_number= k1.Account_number FOR XML PATH('')),1,1,'')
FROM
    Customers k1
GROUP BY
    k1.Account_number
HAVING 
    COUNT(*) > 1
order by Counter

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You want a correlation clause in the subquery:

SELECT k1.Account_number, COUNT(*) as Counter,
       STUFF( (SELECT '; ' + c2.Customer_name 
               FROM Customers c2
               WHERE c2.Account_number = k1.Account_Number
               FOR XML PATH('')
              ), 1, 1, ''
            ) as Customers
FROM Customers k1
GROUP BY k1.Account_number
HAVING COUNT(*) > 1
ORDER BY Counter

Upvotes: 1

Related Questions