CODEWITHSUNDEEP
sqlsql-server-2008
user793468
user793468

Reputation: 4966

transform sql view output

Is it possible to format a sql output of 1 column to 3 columns?

For eg. My current output is:

Column1
   1
   2
   3
   4
   5
   6
   7
   8
   9

here is how I want the output to be:

Column1 Column2 Column3
   1       2       3
   4       5       6
   7       8       9

Upvotes: 0

Views: 47

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You can do this with modulus division and the ROW_NUMBER() function:

;WITH cte AS (SELECT *,ROW_NUMBER() OVER(ORDER BY Column1) RN
              FROM Table1)
     ,cte2 AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY RN%3 ORDER BY Column1) RN2                          
               FROM cte)
SELECT MAX(CASE WHEN RN%3 = 1 THEN column1 END) Col1
      ,MAX(CASE WHEN RN%3 = 2 THEN column1 END) Col2
      ,MAX(CASE WHEN RN%3 = 0 THEN column1 END) Col3
      ,RN2 
FROM cte2
GROUP BY RN2

Example: SQL Fiddle

Upvotes: 1

Related Questions