将列数据拆分为多行 [英] 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)

在 SQL Server 中有什么方法可以做到吗?

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

推荐答案

使用 Jeff 的 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)

这篇关于将列数据拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！