问题描述
我有许多具有 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 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！