transform sql view output

Question

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

For eg. My current output is:

here is how I want the output to be:

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

what logic you are using to put a certain value in a certain column ??? — M.Ali
– M.Ali, Commented Feb 20, 2014 at 20:35
@M.Ali Row2 will go to Column2,Row1 and Row3 will go to Column3,Row1 and so on — user793468
– user793468, Commented Feb 20, 2014 at 20:44

Hart CO · Accepted Answer · 2014-02-20 21:01:43Z

1

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

edited Feb 20, 2014 at 21:01

answered Feb 20, 2014 at 20:42

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user793468 Over a year ago

Values in my question above (1 through 9) are just examples. I have no idea what the values would be. It could be a though z or it could be alpha-numeric values.

user793468 Over a year ago

I get this error: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'JRD1123' to data type int.

Hart CO Over a year ago

@user793468 Yes, a non-numeric field can't be divided, had to add the row number in a separate step. See updated example with animals.

user793468 Over a year ago

What is I have two columns? and I want to display like: 1,a 2,b 3,c... here a,b,c... would be values from second column

Hart CO Over a year ago

@user793468 It would be easiest to concatenate the two columns together in the first step, ie: SELECT col1 +','+ col2 AS NewCol, ROW_NUMBER() OVER(ORDER BY col1, col2) RN in the top line. Then reference NewCol instead of col1 later.

|

Collectives™ on Stack Overflow

transform sql view output

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related