CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
MR K
MR K

Reputation: 113

Split column data into multiple rows

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) enter image description here

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

Upvotes: 2

Views: 15764

Answers (3)

Reihan_amn
Reihan_amn

Reputation: 2727

Simply use LATERAL VIEW EXPLODE function:

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

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

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)

Upvotes: 2

Wajih
Wajih

Reputation: 4383

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.

Upvotes: 2

Related Questions