sql - Denormalize into pipe separated list

I have a tablle like this:

cust   acc
-----------
a      1
b      2

b      3
c      4
c      5
c      6

I want to denormalize above into the following form

cust    acc

---------------
a       1
b       2|3
c       4|5|6

please note that the acc column should now contain a pipe delimited list of accounts for any customer. Also the possible number of accounts for a customer can be variable.

how to do this using SQL ?

Answer

try this:

SELECT cust, acc = 
    STUFF((SELECT '| ' +CAST( acc as varchar(20))
           FROM  b 
           WHERE b.cust = a.cust 
          FOR XML PATH('')), 1, 2, '')
FROM  a
GROUP BY cust








Subscribe to:
Post Comments (Atom)



php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...










database - Android Studio: show username in textview with SQLite
I have an app which needs a login and a registration with SQLite. I have the database and a user can login and register. But i would like th...





c - Enhanced REP MOVSB for memcpy
I would like to use enhanced REP MOVSB (ERMSB) to get a high bandwidth for a custom memcpy .  ERMSB was introduced with the Ivy Bridge micro...





python - An attempt has been made to start a new process before the
current process has finished its bootstrapping phase
I am new to dask and I found so nice to have a module that makes it easy to get parallelization. I am working on a project where I was able ...















Search This Blog