CODEWITHSUNDEEP
stringt-sqlpivotconcatenation
Heinz
Heinz

Reputation: 13

Tsql Pivot on string + concat

I'm having some difficulties with a Pivot query for my particular situation. I have to Pivot a string column over an int column (so just 2 columns) The third column will hold a concatenation of the strings

Example:
CustomerID   Breed
--------------------------
1            Pug
1            Bulldog
1            Doberman
2            Doberman
3            Bulldog
3            Doberman
3            Pug
3            Poedel


Result:    
CustomerID  Pug    Bulldog    Doberman    Poodle    Concat
-----------------------------------------------------------------------------
1           Pug    Bulldog    Doberman    NULL      Pug,Bulldog,Doberman
2           NULL   NULL       Doberman    NULL      Doberman
3           Pug    Bulldog    Doberman    Poodle    Pug,Bulldog,Doberman,Poodle

I have tried the methods described in following post: SQL Server: Examples of PIVOTing String data

But this just returns NULL's everywhere, i'm guessing it's because of my ID column.

Any and all help is greatly appreciated.

Upvotes: 1

Views: 4167

Answers (1)

Taryn
Taryn

Reputation: 247680

You can concatenate the list of breeds using FOR XML PATH and STUFF. The query to get the customerID with the list of breeds will be similar to this:

select customerid, breed,
 STUFF((SELECT DISTINCT ', ' + t2.breed
        FROM yt t2
        WHERE t1.customerid = t2.customerid
        FOR XML PATH('')), 1, 1, '') breedList
from yt t1

See SQL Fiddle with Demo.

Then you can easily incorporate this query into a PIVOT:

;with cte as
(
  select customerid, breed,
     STUFF((SELECT DISTINCT ', ' + t2.breed
            FROM yt t2
            WHERE t1.customerid = t2.customerid
            FOR XML PATH('')), 1, 1, '') breedList
  from yt t1
)
select customerid, Pug, Bulldog, Doberman, Poodle, breedList
from cte
pivot
(
  max(breed)
  for breed in (Pug, Bulldog, Doberman, Poodle)
) piv
order by customerid;

See SQL Fiddle with Demo. This gives a result:

| CUSTOMERID |    PUG | BULLDOG | DOBERMAN | POODLE |                       BREEDLIST |
---------------------------------------------------------------------------------------
|          1 |    Pug | Bulldog | Doberman | (null) |          Bulldog, Doberman, Pug |
|          2 | (null) |  (null) | Doberman | (null) |                        Doberman |
|          3 |    Pug | Bulldog | Doberman | Poodle |  Bulldog, Doberman, Poodle, Pug |

Upvotes: 1

Related Questions