July 30, 2012 at 5:29 am
CREATE TABLE [dbo].[Abc](
[sid] [int] NULL,
[sname] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [Abc]
([sid]
,[sname])
VALUES(1,'xx')
INSERT INTO [Abc]
([sid]
,[sname])
VALUES(2,'yy')
INSERT INTO [Abc]
([sid]
,[sname])
VALUES(3,'zz')
INSERT INTO [Abc]
([sid]
,[sname])
VALUES(3,'zz & vv')
SELECT
DISTINCT ',' + [sname]
FROM [Abc]
ORDER BY ',' + [sname]
FOR
XML PATH ('')
output :
for "zz & vv" its displaying as zz amp; vv
I need output as 'zz & vv' .Can u please help me how to get it. since I need to compare this value..There might several Special characters like this in the column .
July 30, 2012 at 5:49 am
/* Sample data */
CREATE TABLE [dbo].[Abc] ([sid] [int] NULL, [sname] [varchar](20) NULL) ON [PRIMARY];
INSERT INTO [Abc] ([sid], [sname])
VALUES (1, 'xx');
INSERT INTO [Abc] ([sid], [sname])
VALUES (2, 'yy');
INSERT INTO [Abc] ([sid], [sname])
VALUES (3, 'zz');
INSERT INTO [Abc] ([sid], [sname])
VALUES (3, 'zz & vv');
/* Actual Query */
SELECT STUFF((SELECT ',' + [sname]
FROM [Abc]
ORDER BY [sname]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'');
/* Results
------------------
xx,yy,zz,zz & vv
*/
July 30, 2012 at 7:01 am
Thank u ..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply