将字符串拆分为单独的行

Question

我目前正在尝试找出如何在每个“/”的实例中将ID分成2行。原始ID仍将保存在主表和临时表2中，但我需要将新ID保存到新表中。所有这些都发生在报告生成之前的预导入处理程序的临时表中。

当前表格输出如下：

    RWID     RWLEN      DESCR  QTY  UNIT
  T2/10060  20.0000   SomeInfo  1   pcs
  T2/10061  18.5689   SomeInfo  1   pcs
  T2/10062  20.0000   SomeInfo  1   pcs

我需要一个表格来呈现以下内容：

    RWID    RWLEN     DESCR    QTY  UNIT
  T10060   20.0000  SomeInfo    1   pcs
  T20060   20.0000  SomeInfo    1   pcs
  T10061   18.5689  SomeInfo    1   pcs
  T20061   18.5689  SomeInfo    1   pcs
  T10062   20.0000  SomeInfo    1   pcs
  T20062   20.0000  SomeInfo    1   pcs

我的代码片段如下：

-- populate temp table 1 from main table
SELECT *
INTO ##tmp1
FROM main;

-- populate temp table 2 from temp table 1, group and order by RWID
SELECT RWID, MAX(DESCR) as aux
INTO ##tmp2
FROM ##tmp1
group by RWID
ORDER by RWID;

-- populate temp table 3 from temp table 1 then split strings with dividers
SELECT RWID, RWLEN, DESCR, QTY, UNIT
INTO ##tmp3
FROM ##tmp1
UNION ALL
SELECT RWID, NULL RWLEN, NULL DESCR, NULL QTY, NULL UNIT
FROM ##tmp1 
GROUP BY RWID
ORDER BY RWID, DESCR desc;

SELECT 
  RWID = CASE WHEN a.DESCR = b.AUX THEN a.RWID ELSE NULL END,
  RWLEN = CASE WHEN a.DESCR = b.AUX THEN a.RWLEN ELSE NULL END,
  a.DESCR,
  a.QTY,
  a.UNIT

  INTO ##report
  FROM ##tmp3 
  a
  FULL OUTER JOIN ##tmp2
  b on a.RWID = b.RWID;

  SELECT *
  FROM ##report

非常感谢您的时间和帮助。

更新！非常感谢您的所有帮助，它确实引导了我走向正确的方向。我已经找出如何拆分上面显示的字符串以及在示例中没有包含的其他类型的ID。再次感谢您的时间和帮助，你们都很棒！！

结果：http://www.sqlfiddle.com/#!18/17a09/1

- Newbie4598

---

这是针对MySQL还是SQL Server的？ - RoMEoMusTDiE

Answer 1

使用 CTE 的 SQL Server

declare @table table (rwid varchar(30), rwlen float, descr varchar(50), qty int, unit varchar(4))

insert into @table
values 
  ('T2/10060',  20.0000   ,'SomeInfo',  1   ,'pcs'),
  ('T2/10061',  18.5689   ,'SomeInfo',  1   ,'pcs'),
  ('T2/10062',  20.0000   ,'SomeInfo', 1   ,'pcs')



;with mycte as (
   select *,  cast(right(left(rwid,charindex('/',rwid)-1),1) as int) [num], 1 [start] from @table 
   union all
   select t.*,c.start + 1, c.num from @table t
   inner join mycte  c 
     on c.rwid = t.rwid
     and c.start + 1 <= c.num

)
select
concat(left(rwid,1),  start,replace(rwid,left(rwid,charindex('/',rwid)+1),'')) ,
rwlen,
descr,
qty,
unit
 from mycte  
order by rwid, start

- RoMEoMusTDiE

Answer 2

您可以简单地使用以下代码来获取所需的输出：UNION ALL。

SELECT   'T1'+  SUBSTRING(RWID,CHARINDEX('/',RWID)+1,
                         LEN(RWID)- CHARINDEX('/',RWID))  -- + OTHER COLUMN
FROM  [TABLE_NAME]
UNION ALL
SELECT   'T2'+  SUBSTRING(RWID,CHARINDEX('/',RWID)+1,
                         LEN(RWID)- CHARINDEX('/',RWID))  -- + OTHER COLUMN
FROM  [TABLE_NAME]

完整示例

DECLARE @TBL TABLE (RWID VARCHAR(30), RWLEN DECIMAL(15,2), 
                   DESCR VARCHAR(50), QTY INT, UNIT VARCHAR(4))

INSERT INTO @TBL
values 
  ('T2/10060',  20.0000   ,'SomeInfo',  1   ,'pcs'),
  ('T2/10061',  18.5689   ,'SomeInfo',  1   ,'pcs'),
  ('T2/10062',  20.0000   ,'SomeInfo',  1   ,'pcs')

SELECT   'T1'+  SUBSTRING(RWID,  CHARINDEX('/',RWID)+1,LEN(RWID)- CHARINDEX('/',RWID)) RWID
          ,RWLEN, DESCR, QTY, UNIT 
FROM  @TBL
UNION ALL
SELECT   'T2'+  SUBSTRING(RWID,  CHARINDEX('/',RWID)+1,LEN(RWID)- CHARINDEX('/',RWID)) RWID
          ,RWLEN, DESCR, QTY, UNIT 
FROM  @TBL

输出

+---------+-------+----------+-----+------+
| RWID    | RWLEN | DESCR    | QTY | UNIT |
+---------+-------+----------+-----+------+
| T110060 | 20.00 | SomeInfo | 1   | pcs  |
+---------+-------+----------+-----+------+
| T110061 | 18.57 | SomeInfo | 1   | pcs  |
+---------+-------+----------+-----+------+
| T110062 | 20.00 | SomeInfo | 1   | pcs  |
+---------+-------+----------+-----+------+
| T210060 | 20.00 | SomeInfo | 1   | pcs  |
+---------+-------+----------+-----+------+
| T210061 | 18.57 | SomeInfo | 1   | pcs  |
+---------+-------+----------+-----+------+
| T210062 | 20.00 | SomeInfo | 1   | pcs  |
+---------+-------+----------+-----+------+

演示

- PSK

---

如果 RWID 不以 T 开头会怎样？ - RoMEoMusTDiE

如果是这种情况，我们可以使用子字符串来获取第一个字符，而不是硬编码。 - PSK

Answer 3

使用 Cross apply

;WITH CTE( RWID, RWLEN,DESCR,QTY, UNIT)
AS
(
SELECT 'T2/10060',20.0000,'SomeInfo',  1,'pcs' UNION ALL
SELECT 'T2/10061',18.5689,'SomeInfo',  1,'pcs' UNION ALL
SELECT 'T2/10062',20.0000,'SomeInfo',  1,'pcs' 
)
SELECT REPLACE(RWID,'2/1',CAST(Rnk AS VARCHAr(2))) AS RWID
        ,RWLEN
        ,DESCR
        ,QTY
        ,UNIT 
FROM
(
SELECT C.*,ROW_NUMBER()OVER(PARTITION BY C.RWID ORDER BY C.RWID) AS Rnk
FROM CTE C
CROSS APPLY CTE C2
)dt WHERE Rnk<3

Result http://www.sqlfiddle.com/#!18/9eecb/11626

- Sreenu131