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:
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.
Check this out.
Solutions
Post a Comment