SQLTeam.com | Weblogs | Forums

Help for query


#1

Dear SQL Forum,

I want to ask about using cross apply.
I have table like this:

Name|Type|
A|cust|
A|officer|
B|cust|
B|officer|
B|worker|

and i want to the result like this:

Name|Type|
A|cust::officer|
B|cust::officer::worker|

Can you help me for the query please


#2
DECLARE @tTable TABLE
(
    [Name] VARCHAR(50),
    [Type] VARCHAR(50)
)
INSERT INTO @tTable([Name],[Type])
VALUES ('A','cust'),('A','officer'),('B','cust'),('B','officer'),('B','worker')

SELECT DISTINCT    
    T.Name
    ,STUFF(L.[Types],1,2,'') AS [Type]
FROM @tTable AS T
CROSS APPLY
    (SELECT '::' + Tp.[Type]
        FROM @tTable AS Tp
        WHERE T.Name =Tp.Name
        FOR XML PATH('')
    ) AS L ([Types])

#3

thank you so much, your query it works..