SQL沿列移动数据

我什至不知道从哪里开始，但这是我需要做的...

我们有一个包含地址和电话号码的表。但是我需要将6个电话号码列从6减少到3。将数字从右到左移动到任何空单元格中。

下面的例子-

表格看起来像

我希望它看起来像

最佳答案

PIVOT和UNPIVOT将能够完成这项工作。这个想法:

UNPIVOT将数据分成行

清除空行并计算新列为

PIVOT将清理后的数据返回到列中。

这是在一条语句中使用大量CTE的一种方法。注意，我假设有一个ID列，并且已经组成了表名:

;WITH Unpivoted AS
(
    -- our data into rows
    SELECT ID, TelField, Tel
    FROM Telephone
    UNPIVOT
    (
        Tel FOR TelField IN (TEL01,TEL02,TEL03,TEL04,TEL05,TEL06)
    ) as up
),
Cleaned AS
(
    -- cleaning the empty rows
    SELECT
        'TEL0' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TelField) AS VARCHAR) [NewTelField],
        ID,
        TelField,
        Tel
    FROM Unpivoted
    WHERE NULLIF(NULLIF(Tel, ''), 'n/a') IS NOT NULL
),
Pivoted AS
(
    -- pivoting back into columns
    SELECT ID, TEL01, TEL02, TEL03
    FROM
    (
        SELECT ID, NewTelField, Tel
        FROM Cleaned
    ) t
    PIVOT
    (
        -- simply add ", TEL04, TEL05, TEL06" if you want to still see the
        -- other columns (or if you will have more than 3 final telephone numbers)
        MIN(Tel) FOR NewTelField IN (TEL01, TEL02, TEL03)
    ) pvt
)
SELECT * FROM Pivoted
ORDER BY ID

这样一来，电话号码就会移到正确的位置。您还可以将Pivoted中的SELECT * FROM Pivoted更改为任何其他CTE-Unpivoted和Cleaned-以查看部分结果的样子。最后结果: