Convert Comma Separated Column Value to Rows

Turning a Comma Separated string into individual rows

You can use the wonderful recursive functions from SQL Server:

---

Sample table:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

---

The query

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

---

Output

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4        

Convert comma delimited values in a column into rows

If you know the maximum number of values, I think you can split_part():

select t.store, t.location, split_part(products, ',', n.n) as product
 from t join
      (select 1 as n union all
       select 2 union all
       select 3 union all
       select 4
      ) n
      on split_part(products, ',', n.n) <> '';
 

You can also use:

select t.store, t.location, split_part(products, ',', 1) as product
from t 
union all
select t.store, t.location, split_part(products, ',', 2) as product
from t 
where split_part(products, ',', 2) <> ''
union all
select t.store, t.location, split_part(products, ',', 3) as product
from t 
where split_part(products, ',', 3) <> ''
union all
select t.store, t.location, split_part(products, ',', 4) as product
from t 
where split_part(products, ',', 4) <> ''
union all
. . .

How can I get comma delimited values into new row in BigQuery?

Try this one:

with mytable as (
    select "X" as model, "1,34,60,1000" as alarm union all 
    select "Y", "2,4,5,900,1000" union all 
    select "Z", "1"
)
select model, newalarm
from mytable, unnest(split(alarm)) as newalarm

Convert Comma Separated column value to rows

try this

 SELECT A.[id],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [id],  
         CAST ('' + REPLACE([string], ',', '') + '' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a); 

refer here

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html

How can I split comma separated values into multiple rows?

This awk may do:

file:

chr1    10      T       A       2,2     1,1     0,1     1,2     1,0     2
chr1    10      T       G       3       2       1       2       0       0
chr1    10      T       C       5       1,2,3   4,2,1   7       1,8,3   3
chr1    10      T       D       1,2,3,5 4,2,1,8 1,8,3,2 3       5       7

Solution:

awk '{
    n=0;
    for(i=5;i<=NF;i++) {
        t=split($i,a,",");if(t>n) n=t};
    for(j=1;j<=n;j++) {
        printf "%s\t%s\t%s\t%s",$1,$2,$3,$4;
        for(i=5;i<=NF;i++) {
            split($i,a,",");printf "\t%s",(a[j]?a[j]:a[1])
            };
        print ""
        }
    }' file
chr1    10      T       A       2       1       0       1       1       2
chr1    10      T       A       2       1       1       2       1       2
chr1    10      T       G       3       2       1       2       0       0
chr1    10      T       C       5       1       4       7       1       3
chr1    10      T       C       5       2       2       7       8       3
chr1    10      T       C       5       3       1       7       3       3
chr1    10      T       D       1       4       1       3       5       7
chr1    10      T       D       2       2       8       3       5       7
chr1    10      T       D       3       1       3       3       5       7
chr1    10      T       D       5       8       2       3       5       7

Your test input gives:

chr1    10      T       A       2       1       0       1       1       2
chr1    10      T       A       2       1       1       2       1       2
chr1    10      T       G       3       2       1       2       0       0

It does not mater if comma separated values are consecutive, as long as you do not mix 2 or 3 comma on the same line.

---

Related Topics