主页 / dba / 问题 / 210150
Accepted
JamesP
Asked: 2018-06-21 07:25:44 +0800 CST

将多行合并为最少数量的不同值行

  • 772

在 SQL Server 中，是否有人知道将多行数据合并/展平为仅包含不同非空值的尽可能少的行的好方法。

IE

像这样的数据集： 前

像这样： 后

如果有帮助，之前的数据集是一个旋转的行列表，但没有聚合。我无法在数据透视期间聚合它，因为我想保留每个不同的值而不是取 MAX 或 MIN。

我能想到的唯一方法是将数据拆分并将它们重新组合在一起，这不会很有效。

sql-server sql-server-2012

2 个回答

  1. 这里有几种方法，您可以通过为每列的每个非 NULL 值分配任意行号来处理任务（避免无关的排序，因为问题语句不需要有序数据），然后将结果数据转换为最终数据结果集。

    创建测试数据

    CREATE TABLE #table (
    ID INT NOT NULL,
    Col1 VARCHAR(100) NULL,
    Col2 VARCHAR(100) NULL,
    Col3 VARCHAR(100) NULL,
    Col4 VARCHAR(100) NULL,
    Col5 VARCHAR(100) NULL
)
GO
INSERT INTO #table (ID, Col1, Col2, Col3, Col4, Col5)
VALUES  (37850, '1A', NULL, '3A', NULL, '5A'),
        (37850, NULL, NULL, '3B', NULL, NULL),
        (37850, NULL, '2A', '3C', '4A', '5B'),
        (37850, NULL, NULL, NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, NULL, '5C'),
        (37850, NULL, '2B', NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, NULL, '5D'),
        (37850, NULL, NULL, NULL, NULL, NULL),
        (37850, NULL, NULL, NULL, '4B', '5E'),
        (37850, NULL, NULL, NULL, '4C', NULL),
        (37850, NULL, NULL, NULL, '4D', NULL)
GO
CREATE CLUSTERED INDEX CI ON #table (ID)
GO

    执行所需的合并和展平，每列扫描一次数据，最后一组行仅进行一次排序（或哈希组）

    SELECT ID,
        -- Pivot the data for each ID / row number pair,
        MIN(CASE WHEN colNumber = 1 THEN val END) AS Col1,
        MIN(CASE WHEN colNumber = 2 THEN val END) AS Col2,
        MIN(CASE WHEN colNumber = 3 THEN val END) AS Col3,
        MIN(CASE WHEN colNumber = 4 THEN val END) AS Col4,
        MIN(CASE WHEN colNumber = 5 THEN val END) AS Col5
FROM (
    -- Within each ID, assign an arbitrary row number to each non-NULL column value
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 1 AS colNumber, Col1 AS val FROM #table WHERE Col1 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 2 AS colNumber, Col2 AS val FROM #table WHERE Col2 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 3 AS colNumber, Col3 AS val FROM #table WHERE Col3 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 4 AS colNumber, Col4 AS val FROM #table WHERE Col4 IS NOT NULL UNION ALL
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 0) ASC) AS rowNum, 5 AS colNumber, Col5 AS val FROM #table WHERE Col5 IS NOT NULL
) x
GROUP BY ID, rowNum
GO

    在 SQL Server 2017+ 中，执行所需的合并和展平，只需对数据进行一次扫描，无需排序

    SELECT s.ID,
    -- Pivot the data for each ID / row number pair,
    MIN(CASE WHEN v.colNum = 1 THEN v.value END) AS Col1,
    MIN(CASE WHEN v.colNum = 2 THEN v.value END) AS Col2,
    MIN(CASE WHEN v.colNum = 3 THEN v.value END) AS Col3,
    MIN(CASE WHEN v.colNum = 4 THEN v.value END) AS Col4,
    MIN(CASE WHEN v.colNum = 5 THEN v.value END) AS Col5
FROM (
    -- For each ID, build the list of non-NULL values, using
    -- a delimiter that will not exist in your data
    SELECT t.ID,
        STRING_AGG(t.Col1,CHAR(0)) as stringAgg1,
        STRING_AGG(t.Col2,CHAR(0)) as stringAgg2,
        STRING_AGG(t.Col3,CHAR(0)) as stringAgg3,
        STRING_AGG(t.Col4,CHAR(0)) as stringAgg4,
        STRING_AGG(t.Col5,CHAR(0)) as stringAgg5
    FROM #table t
    GROUP BY t.ID
) s
OUTER APPLY (
    -- For each ID, unpivot the list of non-NULL values,
    -- appending an arbitrary row number to each value
    SELECT value, 1 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg1,CHAR(0)) UNION ALL
    SELECT value, 2 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg2,CHAR(0)) UNION ALL
    SELECT value, 3 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg3,CHAR(0)) UNION ALL
    SELECT value, 4 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg4,CHAR(0)) UNION ALL
    SELECT value, 5 AS colNum, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM STRING_SPLIT(s.stringAgg5,CHAR(0))
) v
-- For each ID, group together all column values with same row number
GROUP BY s.ID, v.rn
GO

    可视化测试数据

    在此处输入图像描述

    可视化结果

    在此处输入图像描述

    • 6
  2. Best Answer

    您的数据似乎在各个列值之间缺乏任何关系。如果您可以定义这种关系，您就可以PIVOT适当地处理数据。

    例如，如果您只是想根据值的顺序（基于您的默认排序规则）对齐数据，您可以使用：

    with rawdata as (
select * from (values
    ('00000000-0000-0000-0000-000000037850','Col2','Yes_02')
    ,('00000000-0000-0000-0000-000000037850','Col3','Full marketing schedule')
    ,('00000000-0000-0000-0000-000000037850','Col3','Negotiations started, fell through')
    ,('00000000-0000-0000-0000-000000037850','Col3','No budget')
    ,('00000000-0000-0000-0000-000000037850','Col3','Not interest')
    ,('00000000-0000-0000-0000-000000037850','Col3','Passed to Summerhouse')
    ,('00000000-0000-0000-0000-000000037850','Col4','Darren Waters_01')
    ,('00000000-0000-0000-0000-000000037850','Col4','David Edwards_01')
    ,('00000000-0000-0000-0000-000000037850','Col4','David Simons_01')
    ,('00000000-0000-0000-0000-000000037850','Col4','Jason Gould_01')
    ,('00000000-0000-0000-0000-000000037850','Col4','Martin Thorpe_01')
    ,('00000000-0000-0000-0000-000000037850','Col5','BETT New Exhibitor')
    ,('00000000-0000-0000-0000-000000037850','Col5','BETT Standard Exhibitor')
    ,('00000000-0000-0000-0000-000000037850','Col5','Exhibitor Verified')
    ) x ([ID],[Col],[Value])
    ), ordered as (
select
    ID
    ,Col
    ,[Value]
    ,rn = row_number() over (partition by ID, Col order by [Value])
    from rawdata
    )
select
    ID
    ,[Col1],[Col2],[Col3],[Col4],[Col5]
    from ordered o
    pivot(max([Value]) for Col in ([Col1],[Col2],[Col3],[Col4],[Col5])) pvt
    ;
    • 2

相关问题