SQL Data to layout (SQL 1 Column to multiple column)

Question

I need to make layout from my SQL data. So, I need SQL data one column to multiple column but column or row quantity will be variable. Any ideas please.

Example (SQL column):

Serial
1
2
3
4
5
6

Need look like (if customer I want 2 column than):

1  6  or  1  4
2  5      2  5
3  4      3  6

Answer 1

WITH NumberedRows AS (
    SELECT 
        Serial,
        ROW_NUMBER() OVER (ORDER BY Serial) AS RowNumber
    FROM YourTable
)
SELECT 
    A.Serial AS Column1,
    B.Serial AS Column2
FROM NumberedRows A
JOIN NumberedRows B ON A.RowNumber = B.RowNumber * -1 + (SELECT MAX(RowNumber) + 1 FROM NumberedRows)
WHERE A.RowNumber <= (SELECT MAX(RowNumber) FROM NumberedRows) / 2

This query generates pairs of rows from the Serial column in a layout that matches (1 6, 2 5, 3 4) by utilizing row numbers and joining the table with itself based on the row numbers assumeing the Serial column contains sequential numbers starting from 1.

See this db<>fiddle.