Разделить один столбец на несколько столбцов на сервере SQL в зависимости от количества записей

У меня есть около 28 записей (которые являются динамическими, иногда это нечетное количество записей, а иногда даже) в одном столбце.

Хотелось бы разделить их на несколько столбцов на основе переменной. В каждом столбце должно быть 5 или 6 записей.

Примечание. Эти 5 или 6 записей являются переменной и должны быть изменены.

Пример входной таблицы:

Требуемая выходная таблица будет:

            Col1    col2    col3    col4    col5
            -------------------------------------
            A        F       K       P       U
            B        G       L       Q       V
            C        H       M       R       W
            D        I       N       S       X
            E        J       O       T       Y
                                             Z

Вот что я пробовал, и эти два разных запроса дают разные результаты:

            select Col1
                from Table1 where  Col1 is not null
                order by [col1] asc

--This provides all records

            WITH CTE AS
            (
            SELECT [Col1],
                   (ROW_NUMBER() OVER (ORDER BY Col1) -1)%5 AS Col,
                   (ROW_NUMBER() OVER (ORDER BY Col1) -1)/5 AS Row
            FROM Table1  where  Col1 is not null     
            )
            SELECT [0], [1], [2], [3], [4]
            FROM CTE 
            PIVOT (MAX([COL1]) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
            ORDER BY Row

-- В этом случае записи отсутствуют (когда количество записей велико, так как количество записей может динамически увеличиваться)

            WITH CTE AS
            (
            SELECT [Col1],
                   (ROW_NUMBER() OVER (ORDER BY Col1) -1)%5 AS Col,
                   (ROW_NUMBER() OVER (ORDER BY Col1) -1)/5 AS Row
            FROM Table1  where  Col1 is not null    
            )
            SELECT [0], [1]
            FROM CTE 
            PIVOT (MAX([COL1]) FOR Col IN ([0], [1])) AS Pvt
            ORDER BY Row

Что я делаю не так? Как я могу добиться желаемого результата? Спасибо.

AskMe 20.06.2019 источник

comment

Вы говорите, что должно быть только 5 или 6 строк, значит ли это, что если у вас 500 строк, вам понадобится 100 столбцов? Насколько будет колебаться количество строк и как это повлияет на ожидаемые результаты? - Larnu 20.06.2019

comment

Это пример. Однако мне нужно 5 столбцов по 100 записей в каждом. - AskMe 20.06.2019

Ответы (3)

arrow_upward
3
arrow_downward

Сначала вам нужно выделить столбец для каждой записи, что можно сделать с помощью NTILE():

WITH Table1 AS
(
    SELECT  TOP 26 Col1 = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
    FROM    sys.all_objects
)
SELECT [Col1],
        5 - NTILE(5) OVER(ORDER BY Col1 DESC) AS Col
FROM Table1 ;

Предоставление:

Col1    Col
---------
Z       4
Y       4
X       4
W       4
V       4
U       4
T       3
....
Y       4
Z       4

Обратите внимание, чтобы начать заполнение с последнего столбца, вы должны выделить столбцы в обратном порядке (ORDER BY Col1 DESC), а затем вычесть это из общего количества столбцов.

Затем вы можете отработать свою строку, заказав в своих столбцах:

WITH Table1 AS
(
    SELECT  TOP 26 Col1 = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
    FROM    sys.all_objects
)
SELECT  Col1, Col, ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col1) AS Row
FROM    (   SELECT [Col1],
                    5 - NTILE(5) OVER(ORDER BY Col1 DESC) AS Col
            FROM Table1  
        ) c;

Предоставление:

Col1    Col     Row
--------------------
A       0       1
B       0       2
C       0       3
D       0       4
E       0       5
F       1       1
G       1       2

Затем вы можете применить сводку:

WITH Table1 AS
(
    SELECT  TOP 26 Col1 = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
    FROM    sys.all_objects
), CTE AS
(
    SELECT  Col1, COL, ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col1) AS Row
    FROM    (   SELECT [Col1],
                        5 - NTILE(5) OVER(ORDER BY Col1 DESC) AS Col
                FROM Table1  
            ) c
)
SELECT [0], [1], [2], [3], [4]
FROM CTE 
PIVOT (MAX([COL1]) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row;

Предоставление:

0       1       2       3       4
-------------------------------------
A       F       K       P       U
B       G       L       Q       V
C       H       M       R       W
D       I       N       S       X
E       J       O       T       Y
NULL    NULL    NULL    NULL    Z

ДОПОЛНЕНИЕ

Я настроил образец таблицы и использовал процедуру с динамическим SQL для простоты повторного использования, чтобы продемонстрировать это решение, и, похоже, оно работает так, как я ожидал.

НАСТРОЙКА

-- SET UP TABLE AND INSERT RANDOM VALUES
IF OBJECT_ID(N'tempdb..#Table1', 'U') IS NOT NULL
    DROP TABLE #Table1;

CREATE TABLE #Table1 (Col1 CHAR(2));
INSERT #Table1 (Col1)
SELECT  CONCAT(Letter, Number)
FROM    (SELECT  TOP 26 Letter = CHAR(64 + ROW_NUMBER() OVER(ORDER BY object_id))
        FROM  sys.all_objects) l
        CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) n (Number);

GO
IF OBJECT_ID(N'tempdb..#GenerateMatrix', 'P') IS NOT NULL
    DROP PROCEDURE #GenerateMatrix;

GO
CREATE PROCEDURE #GenerateMatrix @Records INT, @Columns INT
AS
BEGIN
    -- GENERATE COLUMNS FOR PIVOT AND SELECT
    DECLARE @ColSQL NVARCHAR(MAX) = 
            STUFF((SELECT   TOP (@Columns) 
                            CONCAT(',', QUOTENAME(ROW_NUMBER() OVER(ORDER BY Col1) - 1))
                    FROM #Table1
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');


    -- FOR @Cols = 5 Generates "[0],[1],[2],[3],[4]"

    DECLARE @SQL NVARCHAR(MAX) = 
        CONCAT('SELECT ', @ColSQL, '
                FROM (SELECT  Col1, Col, Row = ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col1)
                    FROM    (   SELECT [Col1], Col = @Columns - NTILE(@Columns) OVER(ORDER BY Col1 DESC)
                                FROM (SELECT TOP (@Records) Col1 FROM #Table1 Col1) t
                            ) c) c 
                PIVOT (MAX([COL1]) FOR Col IN (', @ColSQL, ')) AS Pvt
                ORDER BY Row;');

    EXECUTE sp_executesql @SQL, N'@Columns INT, @Records INT', @Columns = @Columns, @Records = @Records;

END
GO

ТЕСТ 1

EXECUTE #GenerateMatrix @Records = 26, @Columns = 5;

0       1       2       3       4
----------------------------------
A1      A6      B2      B7      C3
A2      A7      B3      B8      C4
A3      A8      B4      B9      C5
A4      A9      B5      C1      C6
A5      B1      B6      C2      C7
NULL    NULL    NULL    NULL    C8

ТЕСТ 2

EXECUTE #GenerateMatrix @Records = 8, @Columns = 4;

0       1       2       3
----------------------------
A1      A3      A5      A7
A2      A4      A6      A8

ТЕСТ 3

EXECUTE #GenerateMatrix @Records = 40, @Columns = 8;

0       1       2       3       4       5       6       7
-----------------------------------------------------------
A1      A6      B2      B7      C3      C8      D4      D9
A2      A7      B3      B8      C4      C9      D5      E1
A3      A8      B4      B9      C5      D1      D6      E2
A4      A9      B5      C1      C6      D2      D7      E3
A5      B1      B6      C2      C7      D3      D8      E4

ТЕСТ 4

EXECUTE #GenerateMatrix @Records = 50, @Columns = 6;

0       1       2       3       4       5
    ---------------------------------------
A1      A9      B8      C7      D6      E6
A2      B1      B9      C8      D7      E7
A3      B2      C1      C9      D8      E8
A4      B3      C2      D1      D9      E9
A5      B4      C3      D2      E1      F1
A6      B5      C4      D3      E2      F2
A7      B6      C5      D4      E3      F3
A8      B7      C6      D5      E4      F4
NULL    NULL    NULL    NULL    E5      F5

Ни в одном из тестов нет пропущенных записей и нет строк только с нулевыми значениями.

Пример скрипта БД

GarethD 20.06.2019

comment

Большое спасибо. Количество записей является динамическим (сегодня их 28, завтра может быть 8300, ..), этот скрипт либо отрезает записи, либо добавляет к ним больше NULL (в случае динамических записей). Как этот скрипт может идеально работать с динамическими записями? Я могу исправить количество столбцов до минимума 5 или 10 (максимум). Но количество строк (записей) должно увеличиваться или уменьшаться (в зависимости от динамического количества записей), и не должно быть ни удаления/усечения записей, ни полного столбца (/) как нулевого. ОК, чтобы иметь один или два нуля вместе с фактическими записями в любом столбце. - AskMe; 21.06.2019

comment

Еще одна вещь, английские алфавиты не являются фактическими данными. Это просто приведенный пример. Реальные данные на самом деле имеют тип varchar - AskMe; 21.06.2019

arrow_upward
1
arrow_downward

Опытным путем я смог придумать следующий сводной запрос:

WITH cte AS (
    SELECT Col1, ROW_NUMBER() OVER (ORDER BY Col1) rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN (rn-1) / 5 = 0 THEN Col1 END) AS col1,
    MAX(CASE WHEN (rn-1) / 5 = 1 THEN Col1 END) AS col2,
    MAX(CASE WHEN (rn-1) / 5 = 2 THEN Col1 END) AS col3,
    MAX(CASE WHEN (rn-1) / 5 = 3 THEN Col1 END) AS col4,
    MAX(CASE WHEN (rn-1) / 5 = 4 THEN Col1 END) AS co15
FROM cte
GROUP BY
    (rn-1) % 5;

Демо

Идея здесь в том, что каждая группа (строка) определяется тем, в какой точке по модулю 5 находится текущий номер строки. Столбец или сводная точка определяется тем, сколько кратно 5 номеру текущей строки.

Tim Biegeleisen 20.06.2019

comment

Это не соответствует требуемому результату, в вопросе Z появляется в пятом столбце и 6-й строке. Z вообще не появляется в вашем выводе - GarethD; 20.06.2019

comment

@GarethD Вы предполагаете, что Z принадлежит там или ожидается там, что я не уверен, что это реальный случай. Если ОП хочет хранить больше данных, ему следует увеличить размер общей сетки. - Tim Biegeleisen; 20.06.2019

comment

Я не уверен, что это считается предположением, что именно здесь должен появиться Z, поскольку именно здесь он появляется в вопросе. Вы предполагаете, что образец вывода в вопросе на самом деле не то, что хочет ОП. Я думаю, что это, вероятно, больший скачок, если предположить, что вывод примера - это результат, который хочет OP. - GarethD; 20.06.2019

arrow_upward
0
arrow_downward

Что вы делаете неправильно, так это получение пяти значений в Col,

(ROW_NUMBER() OVER (ORDER BY Col1) -1)%5 AS Col,

но только PIVOTing для 2 из них.

 PIVOT (MAX([COL1]) FOR Col IN ([0], [1])) AS Pvt

Таким образом, вы игнорируете все Col со значениями 2, 3 и 4.

Tab Alleman 20.06.2019

Разделить один столбец на несколько столбцов на сервере SQL в зависимости от количества записей

Ответы (3)

Демо

Похожие вопросы