Query to Merge All Columns Separated with comma -SQL Server

This Query Gives all columns in a single column Separated with comma.

SELECT STUFF((SELECT COLUMN_NAME+',' as [text()] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' for xml path('')),1,0,'')

You can Replace the comma with any symbol by which u want to separate.


'TableName' must be replaced by your table name.

Procedure to Add all rows to a single row in Sql Server

STEP 1.Create Data Base for ex. named 'udb_ankit'  (name your database acc. to you)

STEP 2.Create table (for ex. table, run this script)

USE [udb_ankit]
GO
/****** Object:  Table [dbo].[tbl_keywords]    Script Date: 07/18/2013 15:45:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_keywords](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [keyword] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_keywords] ON
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (1, N'ipod,mobile,playstation')
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (2, N'woofer,sound,mobile,ipod')
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (3, N'sound,ipod,mobile,ipod,playstation,earphone')
INSERT [dbo].[tbl_keywords] ([id], [keyword]) VALUES (4, N'headphone')
SET IDENTITY_INSERT [dbo].[tbl_keywords] OFF


STEP 3.Create Function (which will be used by the procedure to split the fields)

USE [udb_ankit]
GO

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 07/18/2013 15:43:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))      
returns @temptable TABLE (items varchar(MAX))      
as      
begin     
    declare @idx int      
    declare @slice varchar(max)      

    select @idx = 1      
        if len(@String)<1 or @String is null  return      

    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      

        if(len(@slice)>0) 
            insert into @temptable(Items) values(@slice)      

        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break      
    end  
return
end;

GO
STEP 4.Create Procedure (Run this script)

USE [udb_ankit]
GO

/****** Object:  StoredProcedure [dbo].[spInsertNewKeywords1]    Script Date: 07/18/2013 15:42:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spInsertNewKeywords1]

AS
BEGIN
declare @Values VARCHAR(MAX)=NULL
declare @temp1 table
(
    Diffkeywords varchar(max)
)

declare @temp table
(
    ALLkeywords varchar(max)
)
INSERT INTO @temp SELECT STUFF((SELECT DISTINCT  ', ' + keyword AS [text()]
FROM tbl_keywords
FOR XML PATH ('')),1,1,'')

SET @Values=(select top 1 ALLkeywords from @temp)

 INSERT INTO @temp1
    SELECT LTRIM(RTRIM(items))
    FROM [dbo].[Split] (@Values, ',')  -- call the split function
    
 select DISTINCT * from @temp1

END
GO

STEP 5. To see the result Execute Procedure; run "EXEC PROCEDURE_NAME"

For ex:

EXEC  [spInsertNewKeywords1]


------------------------------------XXXXXXXXXXXXXXXX--------------------------------

Create XML from DataBase Data- FOR XML PATH (' ') & STUFF()

Create XML from DataBase Data using FOR XML PATH ( )

 
RUN  select * from tbl_keywords FOR XML PATH (' ')

then u will get,

<id>1</id>
<keyword>ipod,mobile,playstation</keyword>
<id>2</id>
<keyword>woofer,sound,mobile,ipod</keyword>
<id>3</id>
<keyword>sound,ipod,mobile,ipod,playstation,earphone</keyword>
<id>4</id>
<keyword>headphone</keyword>

===============
TO GET THE RESULT IN TEXT FORM USE [text( )]

select * as [text( )] from tbl_keywords FOR XML PATH (' ')
==================

Concepts of STUFF and XML PATH( ' ' )

SELECT STUFF((SELECT keyword FROM tbl_keywords),1,1,'')   // got error

SELECT STUFF((SELECT keyword as [text()] FROM tbl_keywords  for xml path('')),1,1,'')

SELECT STUFF((SELECT ','+keyword as [text()] FROM tbl_keywords  for xml path('')),1,1,'')
=====================