Search

Nov 28, 2008

Display multiple comma separated value into single column output

Here is the data:

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

And we need output as following


output
------
A
B
C
A
D
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.


DECLARE @FieldXml AS XML

DECLARE @v VARCHAR(MAX)

--Creates single row for Field2
SELECT @v = (SELECT ',' + Field2 FROM @Fields FOR XML PATH(''))
--Remove the first comma
SELECT @v = SUBSTRING(@v, 2, LEN(@v))

--Add the XML tag
SELECT @FieldXml = '<F value="' + REPLACE(@v , ',', '" /><F value="') + '" />'

--List single attribute value
SELECT x.value('@value', 'VARCHAR(1)') AS [output]
FROM @FieldXml.nodes('/F') p(x)

2 comments:

Kanna said...

I want the out put as

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

Can you please modify the query as per my requirement.

Imran said...

Check this out.

Solutions