CODEWITHSUNDEEP
sql-servert-sqlssms
Giuseppe Lolli
Giuseppe Lolli

Reputation: 177

Concatenate a String using FOR XML PATH

i have a table:

-----------
-- Testy --
-----------
Dimension      Attribute      Value
----------     ----------     ------
PRODUCT        Name           T-Shirt
PRODUCT        Name           Pants
PRODUCT        Name           Sock
PRODUCT        Name           Wtf
STORE          Name           J-Store
STORE          Name           K-Store
STORE          City           Gotham

I was trying to use FOR XML PATH to make the concatenation of the values to have a result like this:

Dimension      Attribute      Value
----------     ----------     ------
PRODUCT        Name           in ('T-Shirt', 'Pants', 'Sock', 'Wtf')
STORE          Name           in ('J-Store', 'K-Store')      
STORE          City           in ('Gotham')

The table "Testy" is dynamic and can have different vales every day.

My query is the next:

select 
    [Dimension],
    [Attribute],
    [Value] = STUFF((
    SELECT N'', ' ,' + [AttributeValue] FROM Testy x
    WHERE [Dimension] = x.[Dimension] and [Attribute] = x.[Attribute]
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
from Testy
group by [Dimension], [Attribute]

What i'm doing wrong?

Upvotes: 2

Views: 11381

Answers (1)

Luis Cazares
Luis Cazares

Reputation: 3585

You're not qualifying your column names and it's giving preference to the inner query columns.

select 
    [Dimension],
    [Attribute],
    [Value] = STUFF((
        SELECT N'', ' ,' + [AttributeValue] 
        FROM Testy x
        WHERE t.[Dimension] = x.[Dimension] 
        and t.[Attribute] = x.[Attribute]
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
from Testy t
group by [Dimension], [Attribute]

Upvotes: 5

Related Questions