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
?

style="font-weight: bold;">

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










regex - Splitting string and removing whitespace Python
I would like to split a String by comma ','  and remove whitespace from the beginning and end of each split. For example, if I have ...





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# - How to take async result in getter?
I have a method in repository with this implementation which returns a Task Task > GetAllAppsRequestAsync(); I write the getter which cal...















Search This Blog