COALESCE in SQL Server and Reverse it

  The COALESCE function in SQL returns the first non-NULL expression among its arguments. Here we use this function to different purpose. We use COALESCE function to arrange the column values in a single row using comma (,) separator.

USE tempdb

GO

   CREATE TABLE #TEMP (NAME VARCHAR(MAX))

   INSERT INTO #TEMP

SELECT 'C' UNION ALL SELECT 'C++' UNION ALL

SELECT 'JAVA' UNION ALL SELECT 'SQL SERVER' UNION ALL

SELECT 'VB.NET' UNION ALL SELECT 'PHP' UNION ALL

SELECT 'ASP.NET' UNION ALL SELECT 'SAP B1' UNION ALL SELECT 'SAP HANA'

GO   

   SELECT * FROM #TEMP

GO

GO

DECLARE @String VARCHAR(8000)    

SELECT @String = COALESCE(@String +',','') + NAME FROM   

      (SELECT NAME FROM #TEMP)V1   

SELECT @String 

DROP TABLE #TEMP

Now we see the reverse i.e. Arrange a single row value into the column

DECLARE @String VARCHAR(MAX)

SET @String = 'C,C++,JAVA,SQL SERVER,VB.NET,PHP,ASP.NET,SAP B1,SAP HANA'

DECLARE @XML XML

SET @XML = CAST('' + REPLACE(@String, ',','

') + '' AS XML)

SELECT V2.NAME INTO #TEMP

FROM

      (SELECT V1.NAME

       FROM

         (SELECT T.i.value('.', 'VARCHAR(MAX)')'NAME' FROM @XML.nodes('TAG') T(i)

          )V1

      )V2

GO   

SELECT * FROM #TEMP

GO

DROP TABLE #TEMP

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,
Arka Gupta.

http://arkaguptablog.blogspot.in/

https://plus.google.com/+ArkaGupta-arkagupta/posts