split data in sql server

Question

Post reply

split data in sql server

asranantha

SSCarpal Tunnel

Points: 4246
More actions
June 27, 2013 at 10:49 pm

#395055

hi friends i have small doubt in sql server plese tell me how to solve this issuse
table data contains like
tabl name: product
pid , color , price
1 ,red,black,blue , 1000
2 ,blue,yellow , 500
3 ,blue , 750
4 ,white,red,blue,gray , 2500
based on this table data i want show out put like
pid , color , price
1 , red , 1000
1 , black , 1000
1 , blue , 1000
2 , blue , 500
2 , yellow , 500
3 , blue , 750
4 , white , 2500
4 , red , 2500
4 , blue , 2500
4 , gray , 2500
plese tell me query how to solve this issue in sql server.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

Take a look at the two links below. It is a bit of reading, but explains and solves your issue:

Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

Creating a comma-separated list (SQL Spackle)[/url]

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

Please post data in a consumable format so we can work on your problem instead setting it up. The main reason you are struggling with this is because you multiple values in a single attribute. This violates first normal form.

https://en.wikipedia.org/wiki/1NF

This will produce the results you stated you want (notice how I posted a table and populated it with sample data, you should be doing this).

create table #product

(

pid int,

colors varchar(100),

price int

)

insert #product

select 1 , 'red,black,blue', 1000 union all

select 2 , 'blue,yellow' , 500 union all

select 3 , 'blue' , 750 union all

select 4 , 'white,red,blue,gray' , 2500

select p.pid, s.Item as color, p.price

from #product p

cross apply dbo.DelimitedSplit8K(p.colors, ',') s

drop table #product

Follow the link previously suggested or follow the one in my signature about splitting strings. In there you will find the code for the DelimitedSplit8K function.

Then if at all possible, do yourself a favor and split this data into multiple rows permanently so you don't have to keep wrestling with it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/