1

Current Result:

Data
-----
1@2@3@4@5@6

Expected Result:

Data1  Data2  Data3  Data4  Data5  Data6 
----------------------------------------
1      2      3      4      5      6

Tried with SUBSTRING(), LEFT(), CHARINDEX() but didn't help.

Can anybody please tell, what I'm missing?

Santosh Jadi
  • 1,479
  • 6
  • 29
  • 55
  • Is amount of columns always 6? Also, post what exactly you have tried. It will show that you have put some effort before asking a fairly trivial question. – Evaldas Buinauskas Dec 22 '18 at 12:51
  • 1
    You been down this path before - splitting a string using a delimiter. The solution is the same. – SMor Dec 22 '18 at 13:07

2 Answers2

1

Try:

DECLARE @Var NVARCHAR(MAX)

SET @Var ='1@2@3@4@5@6'

DECLARE @XML AS XML

DECLARE @Delimiter AS CHAR(1) ='@'

SET @XML = CAST(('<X>'+REPLACE(@Var,@Delimiter ,'</X><X>')+'</X>') AS XML)

DECLARE @temp TABLE (ID INT,name NVARCHAR(MAX))

INSERT INTO @temp

SELECT N.value('.', 'INT') AS ID, 'data' + CONVERT(Nvarchar,N.value('.', 'INT')) as name 
FROM @XML.nodes('X') AS T(N)

SELECT *
FROM @temp
PIVOT
(
  MAX(id)
  FOR name IN ([data1],[data2],[data3],[data4],[data5],[data6])
) PIV;

Result:

data1   data2   data3   data4   data5   data6
1        2       3       4        5       6

With Dynamic SQL:

SELECT *
INTO ##temp 
FROM @temp

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(name) 
                    from @temp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = N'SELECT * 
            FROM 
            ##temp
            PIVOT 
            (
                MAX(id)
                FOR name IN (' + @cols + N')
            ) PIV'

EXEC(@query)

DROP TABLE ##temp
Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
-1

Try this :

SELECT value FROM STRING_SPLIT('1@2@3@4@5@6', '@');

the result here

mlamsarf
  • 26
  • 6