SQL Rows manipulation

Question

I do have this kind of data in a table:

----------------------------------------
|      price  |  category | categoryID |
----------------------------------------
| 50,100,150  |   Pants   |     1      |
----------------------------------------
| 30,60,90    |   Polo    |     2      |
----------------------------------------

Then I want to put them in separate rows that would look like this:

    ----------------------------------------
    |      price  |  category | categoryID |
    ----------------------------------------
    |       50    |   Pants   |     1      |
    ----------------------------------------
    |      100    |   Pants   |     1      |
    ----------------------------------------
    |      150    |   Pants   |     1      |
    ----------------------------------------
    |       30    |   Polo    |     2      |
    ----------------------------------------
    |       60    |   Polo    |     2      |
    ----------------------------------------
    |       90    |   Polo    |     2      |
    ----------------------------------------

How can I possibly do this in MS SQL? I'm currently trying the XML path but I can't do it with multiple columns.

Thank you.

Duplicate of : https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Brad, Mar 01 '19 at 13:42
You have a bad design, that breaks the most fundamental rule, the 1st Normal Form. A single field should contain a *single* value. If you want to access those prices, it means you need them as individual values. Use a *separate table, eg `ProductPrices` if not `Products`, add the price there and a `CategoryID that acts as a Foreign Key to the Categories table. After all, nobody sells *categories*, they sell products. And products have price, color and various other characteristics — Panagiotis Kanavos, Mar 01 '19 at 13:46
Even in XML you wouldn't have a `category` element that just contained random prices. You'd have `product` elements with the characteristics of each product in the category — Panagiotis Kanavos, Mar 01 '19 at 13:46
The best approach is to fix the broken design. Comma separated values in a column is generally a huge red flag for that. — Jacob H, Mar 01 '19 at 13:47
Thanks everyone for giving your insights. Indeed, it's a bad design. However, this is the data we've been given with. — carl, Mar 01 '19 at 14:29

score 1 · Answer 1 · answered Mar 01 '19 at 14:21

As mentioned in the comments, you have a bad design. However, if you want to continue anyway, you can use STRING_SPLIT function for this.

STRING_SPLIT: A table-valued function that splits a string into rows of substrings, based on a specified separator character.

More info: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

SELECT value AS price,
       category,
       categoryid
FROM   tableName
       CROSS apply STRING_SPLIT(price, ',')

Use this reference to learn more about Database normalization: https://www.tutorialspoint.com/dbms/database_normalization.htm

Thanks for responding. Seems like the STRING_SPLIT is only applicable to SQL 2016. I'm still on an older version, 2008. — carl, Mar 01 '19 at 14:27

score 0 · Answer 2 · answered Mar 01 '19 at 14:34

Use the below function and do a cross apply.

CREATE FUNCTION [dbo].[CommaSeparatedvalues](@value [varchar](max), @splitby[varchar](5))  
RETURNS @RtnValue TABLE (  
 [Id] [int] IDENTITY(1,1) NOT NULL,  
 [Value] [varchar](1000) NULL  
)   
AS   
BEGIN  

While (Charindex(@splitby,@value)>0)  
Begin   
Insert Into @RtnValue (value)  
Select   
    Value = ltrim(rtrim(Substring(@value,1,Charindex(@splitby,@value)-1)))    
    Set @value = Substring(@value,Charindex(@splitby,@value)+len(@splitby),len(@value))  
End   
    Insert Into @RtnValue (Value)  
    Select Value = ltrim(rtrim(@value))  

    Return  
END  


SELECT value AS price,
       category,
       categoryid
FROM   tableName
       CROSS apply CommaSeparatedvalues(price, ',')

SQL Rows manipulation

2 Answers2