問題描述

我有許多具有 1 到 6 個值的行.例如:

I have a number of ROWS that have 1 to 6 values. For example:

Param1: A|B|C|D
Param2: B|Y
Param3: A

我需要像這樣轉換它:

Param1: A
Param1: B
Param1: C
Param1: D
Param2: B
Param2: Y
Param3: A

好吧，我想一個 pivot-unpivot 可能會起作用，但是我需要獲得很多條件和字段.另外，我有一個視圖，可以劃分所有值并對其進行計數.在上面的例子中，它會像這樣返回數據集:

Well, I guess a pivot-unpivot might work, but there are a lot of conditions and fields I need to get. Also, I have a View that divides all values and counts them. In the top example it will return dataset like this:

A 2
B 2
C 1
D 1
Y 1

這是我自己的例子，它在幾條記錄上運行良好，但在超過 100000 行時運行得非常糟糕.

Here is my own example, which is working alright on a few records and works very badly with more than 100000 rows.

最初的故事是關于這個的.我有一些對象(obj)，每個對象都有它的參數(prm)，它們有它的值(val).所以，如您所見，每個對象都像一棵樹，我需要將其展開.這是一個模擬:

Initial story is about this. I have some objects(obj), each has its params(prm), which have its values (val). So, as you see, each object is like a tree, which I need to expand. Here is a simulation:

DECLARE @x TABLE
    (
      prm INT ,
      iin VARCHAR(20) ,
      oout VARCHAR(20)
    ) 
INSERT  INTO @x
VALUES  ( 1, 'A/B/C', 'A' )
INSERT  INTO @x
VALUES  ( 1, 'A/B/C', 'B' )
INSERT  INTO @x
VALUES  ( 1, 'A/B/C', 'C' )
INSERT  INTO @x
VALUES  ( 3, 'D', 'D' )
INSERT  INTO @x
VALUES  ( 2, 'R/G', 'R' )
INSERT  INTO @x
VALUES  ( 2, 'R/G', 'G' )

DECLARE @y TABLE
    (
      obj INT ,
      prm INT ,
      val VARCHAR(20)
    ) 
INSERT  INTO @y
VALUES  ( 10, 1, 'A/B/C' )
INSERT  INTO @y
VALUES  ( 10, 2, 'R/G' )
INSERT  INTO @y
VALUES  ( 10, 3, 'D' )
INSERT  INTO @y
VALUES  ( 20, 2, 'R/G' )
INSERT  INTO @y
VALUES  ( 20, 3, 'D' )

DECLARE @z TABLE
    (
      id INT ,
      obj INT ,
      prm INT ,
      val VARCHAR(20)
    ) 
INSERT  INTO @z
VALUES  ( 1, 10, 1, NULL ) 
INSERT  INTO @z
VALUES  ( 2, 10, 1, NULL ) 
INSERT  INTO @z
VALUES  ( 3, 10, 1, NULL ) 
INSERT  INTO @z
VALUES  ( 4, 10, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 5, 10, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 6, 10, 3, NULL ) 
INSERT  INTO @z
VALUES  ( 7, 20, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 8, 20, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 9, 20, 3, NULL )

和決定:

;
WITH    a AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY prm ORDER BY prm ) n ,
                    *
           FROM     @x
         ),
    b AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY obj, prm ORDER BY obj, prm ) n ,
                    *
           FROM     @z
         )
UPDATE  b
SET     b.val = a.oout
FROM    b
        INNER JOIN @y y ON y.obj = b.obj
                           AND y.prm = b.prm
        INNER JOIN a ON a.n = b.n
                        AND a.prm = b.prm
                        AND y.val = a.iin
SELECT  *
FROM    @z

@y 表 - 是一個像第一個例子一樣帶有參數的表，其中 Param1,Param2 是 prm 列上的 1,2 個 ets，關于 中的某個對象>對象
@z 表 - 模擬 val 設置為 null，表示哪些參數應該填充值
@x 表 - 是對值劃分的模擬，應該應用于 @y 表，替換 @z 的空值包含實際排名值的表格.

@y table - is a table with arguments like the first example, where Param1,Param2 is 1,2 ets on column prm, concerning some object in obj
@z table - is simulation with val set to null, which represents, what params should be filled with values
@x table - is a simulation of dividing of values, that should be applied to @y table, replacing the null values of the @z table with actual ranked values.

有沒有更好的方法來做到這一點?

Is there a better way to do this?

推薦答案

好吧，我不會給你一個完整的解決方案，但如果我需要像這樣拆分數據，我會嘗試使用 sqlxml(您必須在大量行上嘗試以檢查性能是否適合您):

Well I'll not give you a full solution, but if I need split data like this, I'd try to use sqlxml (you have to try it on large number of rows to check if performance ok for you):

declare @x table (prm int,iin varchar(20))

insert into @x values(1, 'A/B/C')
insert into @x values(3, 'D')
insert into @x values(2, 'R/G')

select
    x.prm, x.iin, T.C.value('.', 'nvarchar(max)') as oout
from @x as x
    outer apply (
        select cast('<d>' + replace(x.iin, '/', '</d><d>') + '</d>' as xml) as Data
    ) as D
    outer apply D.Data.nodes('d') as T(C)

參見 sql fiddle 演示 嘗試一下.

這篇關于如何將 1 行的 3 個值應用到每個值的 3 行?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！