Split multiple string's into multiple columns

Question

I have the following three different strings which needs to split into three different columns.

Example:

String 1:

Declare @str1 varchar(max) = 'A1,A2,A3'

String 2:

Declare @str2 varchar(max) = 'B1,B2,B3'

String 3:

Declare @str2 varchar(max) = 'C1,C2,C3'

NoteI want to store the above three strings into three different columns.

Expected Output:

colA   colB   colC
------------------
A1     B1     C1
A2     B2     C2
A3     B3     C3

Attempt:

SQL Fiddle: http://sqlfiddle.com/#!3/d41d8/41345

asked a gazillion times previously. Please try searching.... — Mitch Wheat, Dec 03 '14 at 06:34
@MitchWheat The OP has asked how to split and convert it into columns — Rajesh, Dec 03 '14 at 06:53
you mean like this one: http://stackoverflow.com/questions/27250433/insert-strings-each-values-into-a-single-column — Mitch Wheat, Dec 03 '14 at 07:30

score 2 · Accepted Answer · answered Dec 03 '14 at 07:11

I know its a bit heavy but it will work

Declare @str1 varchar(max) = 'A1,A2,A3'
Declare @str2 varchar(max) = 'B1,B2,B3'
Declare @str3 varchar(max) = 'C1,C2,C3'

DECLARE @RowCount TINYINT
DECLARE @i        TINYINT = 0

DECLARE @Table AS TABLE

(
 colA  varchar(MAX)
,ColB varchar(MAX)
,ColC varchar(MAX)

)

SET @RowCount =  len(@str1) - len(replace(@str1, ',', ''))

WHILE(@i<=@RowCount)
BEGIN
    INSERT INTO @Table
    SELECT  LEFT(@str1,CHARINDEX(',',@str1+',',0)-1) AS colA
           ,LEFT(@str2,CHARINDEX(',',@str2+',',0)-1) AS colB
           ,LEFT(@str3,CHARINDEX(',',@str3+',',0)-1) AS colC

    SET @str1 = STUFF(@str1,1,CHARINDEX(',',@str1,0),'')
    SET @str2 = STUFF(@str2,1,CHARINDEX(',',@str2,0),'')
    SET @str3 = STUFF(@str3,1,CHARINDEX(',',@str3,0),'')

    SET @i = @i + 1

END

SELECT * FROM @Table

Pரதீப் · Answer 2 · 2014-12-03T07:16:00.523

0

If you have atmost three values then try this.

DECLARE @str1 VARCHAR(max) = 'A1,A2,A3'

SELECT Parsename(Replace(@str1, ',', '.'), 3) 'FST_COL',
       Parsename(Replace(@str1, ',', '.'), 2) 'SCD_COL',
       Parsename(Replace(@str1, ',', '.'), 1) 'TRD_COL' into #temp

Or

DECLARE @str1 VARCHAR(max) = 'A1,A2,A3',
        @sql  NVARCHAR(max),
        @loop INT,
        @cnt  INT=1

SELECT @loop = Len(@str1) - Len(Replace(@str1, ',', '')) + 1

SET @sql=' WITH Split_cols ( xmlcol)
     AS (SELECT CONVERT(XML, ''<cols><col>''
                             + Replace('''
         + @str1 + ''', '','', ''</col><col>'') + ''</col></cols>'') as xmlcol)

SELECT '

WHILE @cnt <= @loop
  BEGIN
      SET @sql+=' xmlcol.value(''/cols[1]/col['
                + CONVERT(VARCHAR(30), @cnt)
                + ']'', ''varchar(100)'') AS col'
                + CONVERT(VARCHAR(30), @cnt) + ','
      SET @cnt=@cnt + 1
  END

SET @sql=LEFT(@sql, Len(@sql) - 1)
SET @sql +=' FROM   Split_cols '

--PRINT @sql

EXEC Sp_executesql @sql

edited Dec 03 '14 at 07:16

answered Dec 03 '14 at 06:35

Pரதீப்

91,748
19
131
172

I want to insert the result into `#temp` table. And value of string is dynamic. – MAK Dec 03 '14 at 06:37
@MAK - Just add `INTO #TEMP` at the end. – Pரதீப் Dec 03 '14 at 06:39
Okay! But as I told the values are dynamic. How would I come to know the index value of each value manually here. – MAK Dec 03 '14 at 06:44
Can we do it same way like : http://stackoverflow.com/questions/27255512/string-into-single-column for `MULTIPLE STRINGS`? – MAK Dec 03 '14 at 06:49
@MAK Nope there you want to convert CSV to multiple rows but here it should be multiple columns. – Pரதீப் Dec 03 '14 at 06:50
But here we can't do this for dynamic values. I think so. – MAK Dec 03 '14 at 06:52
@MAK - Check now added another solution. check here for more info http://jahaines.blogspot.in/2009/06/converting-delimited-string-of-values.html – Pரதீப் Dec 03 '14 at 06:55
Nope! This is not the answer which I am expecting. – MAK Dec 03 '14 at 06:57
Sure! You can proceed. – MAK Dec 03 '14 at 07:09

Sanu Antony · Answer 3 · 2014-12-03T09:33:28.303

THIS WILL WORK WITH ANY NUMBER OF STRINGS AND VALUES NOT HARDCODED

CREATE FUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([ID] INT IDENTITY(1,1),[Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END






-- USE THIS PARAMETER TO PASS VALUE
DECLARE @STRING VARCHAR(MAX)    




DECLARE @COUNT INT
DECLARE @I INT
DECLARE @COLUMNNAME VARCHAR(MAX)
DECLARE @CREATETABLE VARCHAR(MAX)
DECLARE @INSERT VARCHAR(MAX)

IF OBJECT_ID('TEMPDB..##TEMPTABLE') IS NOT NULL
    DROP TABLE ##TEMPTABLE
IF OBJECT_ID('TEMPDB..##RETURNLIST') IS NOT NULL
    DROP TABLE ##RETURNLIST
SELECT * INTO ##RETURNLIST FROM dbo.splitstring(@STRING)

select @COUNT = COUNT(*) from ##RETURNLIST
SET @I=0
SET @CREATETABLE = 'CREATE TABLE ##TEMPTABLE ('
WHILE (@COUNT>0)
BEGIN
    SET @COLUMNNAME = 'COLUMN'+ CONVERT(varchar(10), @I) + ' VARCHAR(MAX)'
    SET @CREATETABLE = @CREATETABLE + @COLUMNNAME
    IF(@COUNT<>1)
     SET @CREATETABLE = @CREATETABLE + ', '
    SET @I = @I+1
    SET @COUNT = @COUNT -1;
END
SET @CREATETABLE = @CREATETABLE + ' )'

EXECUTE(@CREATETABLE)

SET @INSERT = 'INSERT INTO ##TEMPTABLE VALUES( '
WHILE (@I>0)
BEGIN

    SET @INSERT = @INSERT +''''+ (SELECT NAME FROM ##RETURNLIST WHERE ID = @COUNT+1) +''''
    IF(@I<>1)
     SET @INSERT = @INSERT + ', '
    SET @I = @I-1
    SET @COUNT = @COUNT +1;
ENDenter code here

SET @INSERT = @INSERT + ' )'

EXECUTE(@INSERT)
EXECUTE('SELECT * FROM ##TEMPTABLE')

Mike D. · Answer 4 · 2014-12-03T10:17:36.730

You haven't given any criteria for joining the values together for each of the rows of columns so I've just joined them in descending order. This solution can handle any number of items in the lists but if the number gets too high you may need to use the MAX RECURSION query hint.

DECLARE @strA NVARCHAR(MAX) = 'A1,A2,A3';
DECLARE @strB NVARCHAR(MAX) = 'B1,B2,B3,B4';
DECLARE @strC NVARCHAR(MAX) = 'C1,C2,C3';

WITH stringData AS (
    --each group of comma separate values with a group identifier
    SELECT 'a' AS grp, @strA AS strng
    UNION ALL
    SELECT 'b' AS grp, @strB
    UNION ALL
    SELECT 'c' AS grp, @strC
),
splitStrings AS (
    --a recursive CTE to split the comma separated values
    SELECT grp, CAST('' AS NVARCHAR(MAX)) AS item,
        strng AS cText
    FROM stringData

    UNION ALL

    SELECT grp,
        CASE
            WHEN CHARINDEX(N',',cText,0)>0 THEN LEFT(cText,CHARINDEX(N',',cText,0)-1) --SUBSTRING(cText,0,CHARINDEX(N',',cText,0))
            ELSE cText
        END,
        RIGHT(cText,LEN(cText)-CHARINDEX(N',',cText,0))
    FROM splitStrings
    WHERE cText!=item
)
SELECT grp,
    item,
    ROW_NUMBER() OVER(PARTITION BY grp ORDER BY item) AS rnum
INTO #stringValues --put the results in a temp table so we don't need to execute the recursive CTE more than once
FROM splitStrings
WHERE len(item)>0;

DECLARE @maxNum INT = (SELECT MAX(rnum) FROM #stringValues);

--join the values together
WITH allNums AS (
    SELECT 1 AS num

    UNION ALL

    SELECT num+1
    FROM allNums
    WHERE num<@maxNum
)
SELECT sa.item AS colA,
    sb.item AS colB,
    sc.item AS colC
FROM allNums
LEFT JOIN #stringValues AS sa ON sa.rnum=allNums.num AND sa.grp='A'
LEFT JOIN #stringValues AS sb ON sb.rnum=allNums.num AND sb.grp='B'
LEFT JOIN #stringValues AS sc ON sc.rnum=allNums.num AND sc.grp='C'

DROP TABLE #stringValues;

Split multiple string's into multiple columns

Attempt:

4 Answers4

Linked