Getting concatenated rows in ascending order

Question

Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?

Script :-

CREATE TABLE Category (
  ID int,
  ErrorMessage nvarchar(200)
)


INSERT INTO Category 
  (ID, ErrorMessage)
VALUES
  (1, 'efgh')

INSERT INTO Category 
  (ID, ErrorMessage)
VALUES
  (1, 'abcd')

INSERT INTO Category 
  (ID, ErrorMessage)
VALUES
  (1, 'ijkl')

INSERT INTO Category 
  (ID, ErrorMessage)
VALUES
  (2, 'efgh')

INSERT INTO Category 
  (ID, ErrorMessage)
VALUES
  (2, 'abcd')

INSERT INTO Category 
  (ID, ErrorMessage)
VALUES
  (2, 'ijkl')

Expected output

Id                 ErrorMessage
----------------------------------
1                  abcd,efgh,ijkl
2                  abcd,efgh,ijkl

Note that the category ids are grouped and the messages separated by comma are in the ascending order.

score 2 · Accepted Answer · answered Apr 19 '11 at 05:07

2

Select C.Id
    , Stuff(
        (
        Select ', ' + C1.ErrorMessage
        From Category As C1
        Where C1.Id = C.Id
        Order By C1.ErrorMessage
        For Xml Path('')
        ), 1, 2, '') As Categories
From Category As C
Group By C.Id

answered Apr 19 '11 at 05:07

Thomas

63,911
12
95
141

You may get XML entitization without using XML type and value extraction – RichardTheKiwi Apr 19 '11 at 05:09

score 2 · Answer 2 · answered Apr 19 '11 at 05:07

2

select A.ID, STUFF((
    select ','+B.ErrorMessage
    from Category B
    where B.ID = A.ID
    order by B.ErrorMessage
    for xml path ('a'), type).value('.','nvarchar(max)'),1,1,'')
from Category A
group by A.ID
order by A.ID

answered Apr 19 '11 at 05:07

RichardTheKiwi

105,798
26
196
262

Getting concatenated rows in ascending order

2 Answers2