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)



c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...









K-PAX ending explanation
In K-PAX , there are two possible explanations: Prot is crazy. He is truly an alien. There are facts that indicate 1, but also 2. Examples: ...





regex - Greedy vs. Reluctant vs. Possessive Quantifiers
I found this excellent tutorial  on regular expressions and while I intuitively understand what "greedy", "reluctant" an...





oop - Accessing private from static method in PHP
Why this works? I mean, accessing the private variable. class Test {     private $q = 0;     public function __construct() {         $this-...















Search This Blog