sql - Denormalize into pipe separated list

Possible Duplicate:
Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?

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





How to solve the java.lang.ArrayIndexOutOfBoundsException: 1 >= 0 error
in my java program?
I got an error in my Java program. I think this happens because of the constructor is not intialized properly. My Base class Program public ...





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















Search This Blog