SQL Query to Split Column Data into Rows

Splitting delimited values in a SQL column into multiple rows

If you are on SQL Server 2016+

You can use the new STRING_SPLIT function, which I've blogged about here, and Brent Ozar has blogged about here.

SELECT s.[message-id], f.value
  FROM dbo.SourceData AS s
  CROSS APPLY STRING_SPLIT(s.[recipient-address], ';') as f;

If you are still on a version prior to SQL Server 2016

Create a split function. This is just one of many examples out there:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

I've discussed a few others here, here, and a better approach than splitting in the first place here.

Now you can extrapolate simply by:

SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;

Also I suggest not putting dashes in column names. It means you always have to put them in [square brackets].

SQL | SPLIT COLUMNS INTO ROWS

From Oracle 12, if it is always two delimited values then you can use:

SELECT t.col1,
       l.col2
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT SUBSTR(col2, 1, INSTR(col2, '-') - 1) AS col2 FROM DUAL
         UNION ALL
         SELECT SUBSTR(col2, INSTR(col2, '-') + 1) FROM DUAL
       ) l

Which, for the sample data:

CREATE TABLE table_name (COL1, COL2) AS
SELECT 1, 'A-B' FROM DUAL UNION ALL
SELECT 2, 'C-D' FROM DUAL UNION ALL
SELECT 3, 'AAA-BB' FROM DUAL;

Outputs:

COL1	COL2
1	A
1	B
2	C
2	D
3	AAA
3	BB

Split columns into rows based on data type

You can unpivot using CROSS APPLY (VALUES, this only requires a single scan of the base table so is very efficient

SELECT
  t.parentid,
  v.ColName,
  v.Charvalue,
  v.IntValue,
  v.TimeValue
FROM YourTable t
CROSS APPLY (VALUES
    ('col1', t.col1, NULL, NULL),
    ('col2', NULL, t.col2, NULL),
    ('col3', NULL, NULL, t.col3)
) v(ColName, Charvalue, IntValue, TimeValue);

Split column data into multiple rows

Using Jeff's DelimitedSplit8K

; 
with cte as
(
    select  id, prodlines, ItemNumber, Item = ltrim(Item),
        grp = dense_rank() over (partition by id order by replace(replace(ltrim(Item), '(Read)', ''), '(Write)', ''))
    from    #prodLines pl
        cross apply dbo.DelimitedSplit8K(prodlines, ',') c
)
select  id, prodlines, prod = stuff(prod, 1, 1, '')
from    cte c
    cross apply
    (
        select  ',' + Item
        from    cte x
        where   x.id    = c.id
        and x.grp   = c.grp
        order by x.Item
        for xml path('')
    ) i (prod)

Need to split column into rows and columns

Try it along this:

Hint: There are some "normal" commas in your sample data.
I suspected these as wrong and used semicolons.
If this is wrong, you might use a general REPLACE() to use ";" instead of ",".

Create a declared table to simulate your issue

DECLARE @tbl TABLE(ID INT, cst VARCHAR(1000));
INSERT INTO @tbl(ID,cst) 
VALUES(1,'string1;3;string2;string3;34;string4;-1;string5;string6;12;string7;5;string8;string9; 65')
     ,(2,'string10;-3;string11;string12;56;string13;6;string14;string15;9');

--The query (for almost any version of SQL-Server, find v2017+ as UPDATE below)

WITH cte AS
(
    SELECT t.ID
          ,B.Nr
          ,A.Casted.value('(/x[sql:column("B.Nr")]/text())[1]','varchar(max)') AS ValueAtPosition
          ,(B.Nr-1) % 5 AS Position
          ,(B.Nr-1)/5 AS GroupingKey
    FROM @tbl t
    CROSS APPLY(SELECT CAST('' + REPLACE(t.cst,';','') + '' AS XML)) A(Casted)
    CROSS APPLY(SELECT TOP(A.Casted.value('count(x)','int')) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) FROM master..spt_values) B(Nr)
)
SELECT ID
      ,GroupingKey
      ,MAX(CASE WHEN Position=0 THEN ValueAtPosition END) AS C1
      ,MAX(CASE WHEN Position=1 THEN ValueAtPosition END) AS C2
      ,MAX(CASE WHEN Position=2 THEN ValueAtPosition END) AS C3
      ,MAX(CASE WHEN Position=3 THEN ValueAtPosition END) AS C4
      ,MAX(CASE WHEN Position=4 THEN ValueAtPosition END) AS C5
FROM cte
GROUP BY ID,GroupingKey
ORDER BY ID,GroupingKey;

The idea in short:

• we use APPLY to add your string casted to XML to the result set. This will help to split the string ("a;b;c" => abc)
• We use another APPLY to create a tally on the fly with a computed TOP-clause. It will return as many virtual rows as there are elements in the XML
• We use sql:column() to grab each element's value by its position and some simple maths to create a grouping key and a running number from 0 to 4 and so on.
• We use GROUP BY together with MAX(CASE...) to place the values in the fitting column (old-fashioned pivot or conditional aggregation).

Hint: If you want this fully generically, with a number of columns not knwon in advance. You cannot use any kind of function or ad-hoc query. You would rather need some kind of dynamic statement creation together with EXEC within a stored procedure.
to be honest: This might be a case of XY-problem. Such approaches are the wrong idea - at least in almost all situations I can think of.

UPDATE for SQL-Server 2017+

You are on v2017, this allows for JSON, which is a bit faster in position safe string splitting. Try this:

    SELECT t.ID
          ,A.*
    FROM @tbl t
    CROSS APPLY OPENJSON(CONCAT('["',REPLACE(t.cst,';','","'),'"]')) A

The general idea is the same. We transform a string to a JSON-array ("a,b,c" => ["a","b","c"]) and read it with APPLY OPENJSON().
You can perform the same maths at the "key" column and do the rest as above.

Just because it is ready here, this is the full query for v2017+

WITH cte AS
(
    SELECT t.ID
          ,A.[key]+1 AS Nr
          ,A.[value] AS ValueAtPosition
          ,A.[key] % 5 AS Position
          ,A.[key]/5 AS GroupingKey 
    FROM @tbl t
    CROSS APPLY OPENJSON(CONCAT('["',REPLACE(t.cst,';','","'),'"]')) A
)
SELECT ID
      ,GroupingKey
      ,MAX(CASE WHEN Position=0 THEN ValueAtPosition END) AS C1
      ,MAX(CASE WHEN Position=1 THEN ValueAtPosition END) AS C2
      ,MAX(CASE WHEN Position=2 THEN ValueAtPosition END) AS C3
      ,MAX(CASE WHEN Position=3 THEN ValueAtPosition END) AS C4
      ,MAX(CASE WHEN Position=4 THEN ValueAtPosition END) AS C5
FROM cte
GROUP BY ID,GroupingKey
ORDER BY ID,GroupingKey;

SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

Convert the data, split rows into columns in SQL

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.CountryName)
FROM #temptest c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT ObjectName, ' + @cols + ' from
(
select ObjectName,CountryName,counts
from #temptest
) x
pivot
(
max(counts)
for CountryName in (' + @cols + ')
) p '

execute(@query)

---

Related Topics

Get the Week Start Date and Week End Date from Week Number

How to Query a Tree Structure Table in MySQL in a Single Query, to Any Depth

Date Difference Between Consecutive Rows

How to Create Composite Primary Key in SQL Server 2008

Use MySQL Spatial Extensions to Select Points Inside Circle

SQL Performance on Left Outer Join VS Not Exists

Stored Procedure or Function Expects Parameter Which Is Not Supplied

MySQL: Split Comma Separated List into Multiple Rows

Constraint Defined Deferrable Initially Immediate Is Still Deferred

A Beginner'S Guide to SQL Database Design

MySQL Search and Replace Some Text in a Field

Difference Between Natural Join and Inner Join

SQL Server - Stop or Break Execution of a SQL Script

SQL Switch/Case in 'Where' Clause

SQL - If Exists Update Else Insert Into

Does Count(*) Always Return a Result

MySQL Number of Items Within "In Clause"

Run All SQL Files in a Directory

---

Leave a reply

Submit