CODEWITHSUNDEEP
sqlsql-serverxml
s-a-n
s-a-n

Reputation: 787

Output a table row as XML with Groupby another Column

I have the following table:

Dept     Skills
1        200
1        250
2        150
2        200
2        250

and require this output:

Dept      XMLcolumn
1         <xcol>200></xcol><xcol>250</xcol>
2         <xcol>150></xcol><xcol>200</xcol><xcol>250</xcol>

I tried FOR XML PATH without much success: 

select dept, skills from table for xml path('xcol'), type

Any help is greatly appreciated!

Upvotes: 1

Views: 51

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166406

How about something like

DECLARE @TABLE TABLE(
        Dept INT,
        Skills INT
)

INSERT INTO @TABLE VALUES ( 1,200),( 1,250),( 2,150),( 2,200),( 2,250)

SELECT  Dept,
        (
            SELECT  Skills xcol
            FROM    @TABLE
            WHERE   Dept = t.Dept
            FOR XML RAW(''), ELEMENTS
        )
FROM    @TABLE t
GROUP BY    Dept

SQL Fiddle DEMO

Upvotes: 2

TechDo
TechDo

Reputation: 18639

Please try:

SELECT Dept,
    (SELECT '<xcol>' + CAST(Skills as nvarchar)+ '</xcol>'
       FROM tbl a
       where a.Dept=b.Dept
        FOR XML PATH(''),type).value('.','nvarchar(max)') XMLColumn
FROM tbl b
group by Dept

Upvotes: 2

Related Questions