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)



hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...










Windows Boot Manager can't boot grubx64.efi
I tried adding grubx64.efi in the Windows Boot Manager using BCDEdit. However when I boot up my computer and try to start GRUB from Windows ...





windows 7 - RDP logout using bat file
Using Windows 7. When I RDP to a PC I'd like to be able to logout of the session without the screen reverting to a Ctrl+Alt+Del Login sc...





linux - Ubuntu error "Gave up waiting for root device" on 9.10 Netbook
Remix
So I installed Ubuntu Netbook Remix 9.10 onto my Asus EeePC 1008HA netbook. It worked perfectly and was pretty quick. Restarting, suspending...















Search This Blog