Search

Dec 16, 2008

Display multiple comma separated value into single column output Part II

Here is the data:

Field1      Field2
----------- --------------------
1 A,B,C
2 A
3 D,G


And we need output as following

Field1               ID
-------------------- ----
1 A
1 B
1 C
2 A
3 D
3 G

Lets create data first.

DECLARE @Fields AS TABLE
(
Field1 INT,
Field2 VARCHAR(20)
)

INSERT INTO @Fields VALUES (1,'A,B,C')
INSERT INTO @Fields VALUES (2,'A')
INSERT INTO @Fields VALUES (3,'D,G')

Here is the query for getting expected result.

SET @AnswerXML =
REPLACE(REPLACE((SELECT
Field1, '<Ids><Id value="' + REPLACE(Field2, ',','" /><Id value="') + '" /></Ids>'
FROM @Fields
FOR XML PATH ('F')
), '&lt;', '<'), '&gt;', '>')

SELECT
Answer.value('Field1[1]', 'BIGINT') as Field1
,y.value('@value[1]', 'VARCHAR(1)') AS ID
FROM
@AnswerXML.nodes('/F') p(Answer)
OUTER APPLY Answer.nodes('Ids/Id') o(y)

Also check Display multiple comma separated value into single column output Part I

1 comment:

Maulik Dhorajia said...

Great this was really helpfull, I was trying it with the different way but this one is a better one!