Split column data into multiple rows

Question

I have data currently in my table like below under currently section. I need the selected column data which is comma delimited to be converted into the format marked in green (Read and write of a category together)

Any ways to do it in SQL Server?

Please do look at the proposal data carefully.... Maybe I wasn't clear before: this isn't merely the splitting that is the issue but to group all reads and writes of a category together(sometimes they are just merely reads/writes), it's not merely putting comma separated values in multiple rows.

--    script:
    use master 
    Create table prodLines(id int  , prodlines varchar(1000))
    --drop table prodLines
    insert into prodLines values(1, 'Automotive Coatings (Read), Automotive Coatings (Write), Industrial Coatings (Read), S.P.S. (Read), Shared PL''s (Read)')
    insert into prodLines values(2, 'Automotive Coatings (Read), Automotive Coatings (Write), Industrial Coatings (Read), S.P.S. (Read), Shared PL''s (Read)')

    select * from prodLines

did you try to search for it ? there are many many such example ever ask in SO . google.com.sg/#q=split+csv+string+to+rows+in+sql+server — Squirrel
– Squirrel, Commented Apr 12, 2016 at 6:44
Instead of posting an image, post a consumable create and insert script for us to work with. — Felix Pamittan
– Felix Pamittan, Commented Apr 12, 2016 at 6:51
See my edits , i have tried to explain , why it isnt the same — MR K
– MR K, Commented Apr 12, 2016 at 6:52
basically still the same. Use a csv splitter to split into multiple rows and then you concatenate back based on your rules of "same category". — Squirrel
– Squirrel, Commented Apr 12, 2016 at 7:02

Squirrel · Accepted Answer · 2016-04-12 07:11:11Z

2

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)

answered Apr 12, 2016 at 7:11

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MR K Over a year ago

Unable to get the above to work , do you have the entire workable script ?

Squirrel Over a year ago

That is the complete query. Did you create the DelimitedSplit8K function ?

Wajih · Accepted Answer · 2016-04-12 06:46:41Z

2

Take a look at STRING_SPLIT, You can do something like:

SELECT user_access
FROM Product
CROSS APPLY STRING_SPLIT(user_access, ',');

or what ever you care about.

answered Apr 12, 2016 at 6:46

Wajih

4,4152 gold badges28 silver badges42 bronze badges

2 Comments

Zohar Peled Over a year ago

Please note that STRING_SPLIT is available only on sql server 2016 or Azure.

MR K Over a year ago

hmm, mine is 2008 , so thats out the window

Reihan_amn · Accepted Answer · 2019-03-14 21:22:09Z

0

Simply use LATERAL VIEW EXPLODE function:

select access  from Product
LATERAL VIEW explode(split(user_access, '[,]')) usrAccTable AS access;

answered Mar 14, 2019 at 21:22

Reihan_amn

2,7572 gold badges24 silver badges23 bronze badges

Collectives™ on Stack Overflow

Split column data into multiple rows

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related