CODEWITHSUNDEEP
sqlsql-server
Sabyasachi Mukherjee
Sabyasachi Mukherjee

Reputation: 303

SQL query to return matrix

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 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

Upvotes: 2

Views: 1592

Answers (2)

claus
claus

Reputation: 425

In general:

  • SQL is for retrieving data, that is all your X records in one column
  • Making a nice display of your data is usually the job of the software that queries SQL, e.g. your web/desktop application.

However if you really want to build the display output in SQL you could use a WHILE loop in connection with LIMIT and PIVOT. You would just select the first 5 records, than the next ones until finished.

Here is an example of how to use WHILE: http://msdn.microsoft.com/de-de/library/ms178642.aspx

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453707

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

Upvotes: 3

Related Questions