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)



casting - Why wasn't Tobey Maguire in The Amazing Spider-Man? -
Movies & TV

In the Spider-Man franchise, Tobey Maguire  is an outstanding performer as a Spider-Man and also reprised his role in the sequels Spider-Man...









php - Looping through a table with Simple HTML DOM
I'm using Simple HTML DOM to extract data from a HTML document, and I have a couple of issues that I need some help with. On the line th...





php - Undefined property: CI_Loader::$input
I've just started to learn CodeIgniter and gotten trouble with example below:  My Controller:          class Site extends CI_Controller ...





Difference between ListBuffer and ArrayBuffer in scala
          What is the difference between a ListBuffer and an ArrayBuffer in scala. What is the advantage of using one over the other ? Thank...















Search This Blog