SQL query to return matrix

Question

I have a set of rows with one column of actual data. The goal is display this data in Matrix format. The numbers of Column will remain same, the number of rows may vary.

For example:

I have 20 records. If I have 5 columns - then the number of rows would be 4
I have 24 records. I have 5 columns the number of rows would be 5, with the 5th col in 5th row would be empty.
I have 18 records. I have 5 columns the number of rows would be 4, with the 4th & 5th col in 4th row would be empty.

I was thinking of generating a column value against each row. This column value would b,e repeated after 5 rows. But I cannot the issue is "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations"

Not sure how it can be achieved.

Any advice will be helpful.

Further Addition - I have managed to generate the name value association with column name and value. Example -
Name1 Col01
Name2 Col02
Name3 Col03
Name4 Col01
Name5 Col02

You wish to split column to multiple parts when SQL is returning results or when displaying them on web? — Justinas

Martin Smith Martin Smith · Accepted Answer · 2014-08-21T07:36:58

You can use ROW_NUMBER to assign a sequential integer from 0 up. Then group by the result of integer division whilst pivoting on the remainder.

WITH T AS
(
SELECT number, 
        ROW_NUMBER() OVER (ORDER BY number) -1 AS RN
FROM master..spt_values
)
SELECT MAX(CASE WHEN RN%5 = 0 THEN number END) AS Col1,
       MAX(CASE WHEN RN%5 = 1 THEN number END) AS Col2,
       MAX(CASE WHEN RN%5 = 2 THEN number END) AS Col3,
       MAX(CASE WHEN RN%5 = 3 THEN number END) AS Col4,
       MAX(CASE WHEN RN%5 = 4 THEN number END) AS Col5
FROM T
GROUP BY RN/5
ORDER BY RN/5

SQL query to return matrix

2 Answers