Question

我有一个带有两列的表格,该表格只包含一个值,但有些条目含有两个或三个值。所有其他列对于这些问题行都是相同的。

Table A - Currently 
Deal ID | PA ID | other columns 
1         2       xxxxx
1,2       2       xxxxx
3         1,5     xxxxx

我想要的

Deal ID | PA ID | other columns 
1         2       xxxxx
1         2       xxxxx
2         2       xxxxx
3         1       xxxxx
3         5       xxxxx

我不知道该如何做?? 认为我需要 UNPIVOT 然后删除, 。

Answer 1

这是一个解决方案。它是野蛮的武力, 并使用工会所有带来多份副本:

with incols as (
     select (case when charindex(Dealid,  , ) > 0
                  then left(DealId, charindex(Dealid,  , ) - 1)
                  else DealId
             end) as DealId1,
            (case when charindex(Dealid,  , ) > 0
                  then substring(DealId, charindex(DealId,  , ) + 1, 100)
             end) as DealId2,
            (case when charindex(PAId,  , ) > 0
                  then left(PAId, charindex(PAId,  , ) - 1)
                  else PAId
             end) as PAId1,
            (case when charindex(PAId,  , ) > 0
                  then substring(PAId, charindex(PAId,  , ) + 1, 100)
             end) as PAId2,
            t.*
     from t
    ),
    deals as (
     select (case when whichdeal = 1 then deal1 else deal2 end) as newdeal, t.*
     from ((select *, 1 as whichdeal
            from t
           ) union all
           (select *, 2 as whichdeal
            from t
            where deal2 is not null
           ))  t
    )
select newdeal as dealid, t.*
from deals

包括 PA 需要添加另一个 CTE, 然后在交易和 PA id 上加入交易和 PA 来获得所有可能的组合。当两行都出现重复时, 您没有具体说明您想要发生什么, 所以我只是猜测您想要所有的组合。

Answer 2

解决办法是:

DECLARE @t TABLE ( 
  DealID VARCHAR(10), 
  PAID   VARCHAR(200), 
  [DESC] VARCHAR(100)) 

INSERT @t 
SELECT  1 , 
        2 , 
        xxxx  
UNION ALL 
SELECT  1,2 , 
        2 , 
        xxxx  
UNION ALL 
SELECT  3 , 
        1,5 , 
        xxxx  

SELECT LEFT(b, Charindex( , , b +  , ) - 1) AS DealID, 
       LEFT(d, Charindex( , , d +  , ) - 1) AS PAID, 
       [Desc] 
FROM   (SELECT Substring(DealID, DE.number, 200) AS b, 
               Substring(PAID, PA.number, 200)   AS d, 
               [Desc] 
        FROM   @t DealID 
               LEFT JOIN (SELECT DISTINCT number 
                          FROM   master.dbo.spt_values 
                          WHERE  number BETWEEN 1 AND 200) PA 
                 ON Substring( ,  + PAID, PA.number, 1) =  ,  
               LEFT JOIN (SELECT DISTINCT number 
                          FROM   master.dbo.spt_values S 
                          WHERE  number BETWEEN 1 AND 200) DE 
                 ON Substring( ,  + DealID, DE.number, 1) =  , ) t

友情链接