How to write an SQL query that expands a comma delimited field into multiple fields?

Question

First of all...I know it's bad to have comma separated values in tables and no I'm not able to change it.

I have several tables that contain the following data:

************** Table 1 **********
stock_id   products_id   stock_attributes 
   5271       279           1559,2764

************** Table 2 *********************
products_attributes_id   products_id   options_id   options_values_id
   1559                     279             2               8
   2764                     279             3               63

************** Table 3 ************************
products_options_id     products_options_name
    2                       Size
    3                       Color

************** Table 4 *****************
products_options_values_id      products_options_values_name
    14                                  Pink
    63                                  Mint
    13                                  Black
    8                                   S
    9                                   M
    10                                  L
    11                                  XL

What I'd like to do is create a query to take the field stock_attributes in Table 1 and expand it using the information in the Tables 2, 3 & 4 so I end up with the following:

*********** Resulting Table **********
stock_id    products_id     opt1  opt2  opt3   opt4     
5271        279             Size  S     Color  Mint

I can do this programmatically after the fact but I'm curious if it can be done in a single SQL query. I've found similar questions and answer on how to select a particular value from a comma delimited field but nothing to do this. Any help is appreciated.

which one? mysql or sqlserver? those are different querys. Also what is quantity `value = 2` ? — Juan Carlos Oropeza, May 16 '16 at 01:42
For MySQL have a look at: https://www.periscopedata.com/blog/splitting-comma-separated-values-in-mysql.html — Alex, May 16 '16 at 01:48
@user365 . . . I removed the extraneous database tags. Please add back the tag for the database you are actually using. — Gordon Linoff, May 16 '16 at 01:50
Thanks sorry it's for MySQL. Also I removed the quantity column in the result. I attempted to cut everything down to the minimum needed to illustrate the problem and obviously didn't cut out everything. — user365, May 17 '16 at 01:58

score 0 · Answer 1 · answered May 16 '16 at 02:19

If the number of attributes are fixed. You can simply split the column into multiple like this.

SELECT stock_id, products_id,
    PARSENAME(REPLACE(stock_attributes,',','.'),2) a1, 
PARSENAME(REPLACE(stock_attributes,',','.'),1) a2
FROM Table1

To further expand and include the values from other tables, you can do simple JOIN

select pTable.stock_id, pTable.products_id, 
    (select products_options_name from Table3 where products_options_id = t2.options_id) as opt1,
    (select products_options_values_name from Table4 where products_options_values_id = t2.options_values_id) as opt2,
    (select products_options_name from Table3 where products_options_id = t3.options_id) as opt3,
    (select products_options_values_name from Table4 where products_options_values_id = t3.options_values_id) as opt4
from 
(
    SELECT stock_id, products_id,
        PARSENAME(REPLACE(stock_attributes,',','.'),2) a1, 
    PARSENAME(REPLACE(stock_attributes,',','.'),1) a2
    FROM Table1
) pTable
join Table2 t2 on pTable.a1 = t2.products_attributes_id and pTable.products_id = t2.products_id   
join Table2 t3 on pTable.a2 = t3.products_attributes_id and pTable.products_id = t3.products_id

score 0 · Answer 2 · edited May 23 '17 at 12:15

This is very long. So I stop until the point where have the data ready for a Dynamic SQL Pivot

This can solve any number of atributes. Just save the output to a table o create a stored procedure to have the data for the dynamic pivot.

tmp: Split the comma-separated values into rows
product_attributes: is just a select to see the result from the recursive function. Can't be removed
product_details: join the attributes with the other tables to get their values
product_attribute_count: each attribute needs a row_number so you can create header later. Also I realize you didnt need the COUNT()
product_pre_pivot: create headers and value, for option_id and option_value_id
product_pivot: separated those pair header into a single column

You can test each step to see the result .... SELECT * FROM [STEP#]

SQL Fiddle Demo

Code:

;WITH tmp([stock_id], [products_id], products_attributes_id, data_r) AS   
(
    SELECT 
        [stock_id], [products_id], 
        LEFT([stock_attributes], Charindex(',', [stock_attributes] + ',') - 1), 
        STUFF([stock_attributes], 1, Charindex(',', [stock_attributes] + ','), '') 
    FROM   
        Table1 

    UNION ALL 

    SELECT 
        [stock_id], [products_id], 
        LEFT(data_r, Charindex(',', data_r + ',') - 1), 
        STUFF(data_r, 1, Charindex(',', data_r + ','), '') 
    FROM   
        tmp 
    WHERE  
        data_r > ''
), product_attributes AS 
(         
    SELECT 
        [stock_id], [products_id], 
        [products_attributes_id]
    FROM   
        tmp 
), product_details AS 
(        
    SELECT 
        pa.*, 
        t2.options_id, t2.options_values_id,
        t3.[products_options_name],
        t4.[products_options_values_name]
    FROM 
        product_attributes pa
    JOIN 
        Table2 t2 ON pa.[products_id] = t2.[products_id]
                  AND pa.[products_attributes_id] = t2.[products_attributes_id]
    JOIN 
        Table3 t3 ON t2.[options_id] = t3.[products_options_id] 
    JOIN 
        Table4 t4 ON t2.[options_values_id] = t4.[products_options_values_id]
), product_attribute_count AS 
(           
    SELECT 
        *,
        row_number() over (PARTITION BY products_id 
                           ORDER BY options_id) as rn,
        count(*) over (partition by products_id) cnt
    FROM 
        product_details
), product_pre_pivot AS 
( 
    SELECT 
        stock_id, products_id,    
        'opt' + CAST(2*rn - 1 as varchar(max))  as header1,
        products_options_name as d_value,
        'opt' + CAST(2*rn  as varchar(max)) as header2,
        products_options_values_name as a_value
    FROM 
        product_attribute_count
), product_pivot AS 
(  
    SELECT 
        stock_id, products_id,
        header1 header, d_value value
    FROM 
        product_pre_pivot

    UNION ALL

    SELECT 
        stock_id, products_id,
        header2 header, a_value value
    FROM 
        product_pre_pivot 
)
SELECT *
FROM product_pivot
ORDER BY header

OUTPUT

| stock_id | products_id | header | value |
|----------|-------------|--------|-------|
|     5271 |         279 |   opt1 |  Size |
|     5271 |         279 |   opt2 |     S |
|     5271 |         279 |   opt3 | Color |
|     5271 |         279 |   opt4 |  Mint |

How to write an SQL query that expands a comma delimited field into multiple fields?

2 Answers2