How to convert my data from row to column ?

Question

I am using sqlserver 2008 and I have a data like below. The row count is uncertain. The row count is 44 for this simple, but it can be more or less. And The scale always is constant.

+----------------------------------------------------+-------+
|                       Level                        | Count |
+----------------------------------------------------+-------+
| 0 - 10                                             |    49 |
| 11 - 20                                            |    11 |
| 21 - 30                                            |    15 |
| 31 - 40                                            |    19 |
| 41 - 50                                            |    18 |
| 51 - 60                                            |     9 |
| 61 - 70                                            |     0 |
| 71 - 80                                            |     2 |
| 81 - 90                                            |     2 |
| 91 - 100                                           |     1 |
| 101 - 9999                                         |     9 |
| 0 - 10                                             |    47 |
| 11 - 20                                            |     6 |
| 21 - 30                                            |     5 |
| 31 - 40                                            |     3 |
| 41 - 50                                            |     3 |
| 51 - 60                                            |     5 |
| 61 - 70                                            |     9 |
| 71 - 80                                            |     5 |
| 81 - 90                                            |     8 |
| 91 - 100                                           |    14 |
| 101 - 9999                                         |    30 |
| 0 - 10                                             |    46 |
| 11 - 20                                            |     3 |
| 21 - 30                                            |     4 |
| 31 - 40                                            |     4 |
| 41 - 50                                            |     4 |
| 51 - 60                                            |     1 |
| 61 - 70                                            |     7 |
| 71 - 80                                            |    14 |
| 81 - 90                                            |    13 |
| 91 - 100                                           |    15 |
| 101 - 9999                                         |    24 |
| 0 - 10                                             |    43 |
| 11 - 20                                            |     4 |
| 21 - 30                                            |     3 |
| 31 - 40                                            |     1 |
| 41 - 50                                            |     7 |
| 51 - 60                                            |     3 |
| 61 - 70                                            |     8 |
| 71 - 80                                            |    15 |
| 81 - 90                                            |    13 |
| 91 - 100                                           |    17 |
| 101 - 9999                                         |    21 |
+----------------------------------------------------+-------+

I would like it to be like below,

+--------+---------+---------+---------+---------+---------+---------+---------+---------+----------+-------+
| 0 - 10 | 11 - 20 | 21 - 30 | 31 - 40 | 41 - 50 | 51 - 60 | 61 - 70 | 71 - 80 | 81 - 90 | 91 - 100 | 101 - |
+--------+---------+---------+---------+---------+---------+---------+---------+---------+----------+-------+
|     49 |      11 |      15 |      19 |      18 |       9 |       0 |       2 |       2 |        1 |     9 |
|     47 |       6 |       5 |       3 |       3 |       5 |       9 |       5 |       8 |       14 |    30 |
|     46 |       3 |       4 |       4 |       4 |       1 |       7 |      14 |      13 |       15 |    24 |
|     43 |       4 |       3 |       1 |       7 |       3 |       8 |      15 |      13 |       17 |    21 |
+--------+---------+---------+---------+---------+---------+---------+---------+---------+----------+-------+

How can I do this ?

[You][1] [could][2] [try][3] [existing][4] [questions][5]. [1]: http://stackoverflow.com/questions/10806302/sql-server-rows-to-columns [2]: http://stackoverflow.com/questions/14857737/sql-server-rows-to-columns-without-aggregation [3]: http://stackoverflow.com/questions/9784978/rows-to-columns-sql-server [4]: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008 [5]: http://stackoverflow.com/questions/15091330/sql-server-convert-columns-to-rows — Rory, Mar 21 '14 at 12:22

Marwie · Accepted Answer · 2014-03-21T13:03:30.290

You can use the pivot keyword to pivot a row entry into a column.

SELECT  [0 - 10], 
    [11 - 20], 
    [21 - 30], 
    [31 - 40], 
    [41 - 50], 
    [51 - 60], 
    [61 - 70], 
    [71 - 80], 
    [81 - 90], 
    [91 - 100], 
    [101 - 9999] 
FROM (
        SELECT row_number() over(partition by [Level] order by [Count]) as [RowNumber], 
                [Level], 
                [Count] 
            FROM TableName 
     )  as t
            PIVOT (MIN([Count])
                FOR [Level] IN ([0 - 10], [11 - 20], [21 - 30], [31 - 40], [41 - 50], [51 - 60], [61 - 70], [71 - 80], [81 - 90], [91 - 100], [101 - 9999])
                ) as p

score 0 · Answer 2 · answered Mar 21 '14 at 12:28

0

SELECT MAX(CASE WHEN LEVEL='0 - 10' THEN Count ELSE 0 END) as '0 - 10',
       MAX(CASE WHEN LEVEL='11 - 20' THEN Count ELSE 0 END) as '11 - 20',
....
FROM table
GROUP BY LEVEL

answered Mar 21 '14 at 12:28

Mihai

26,325
7
66
81

score 0 · Answer 3 · answered Mar 21 '14 at 12:35

I think the following query will help you.

[LevelTable] is the your table and the columns are [Level] and [Count]

SELECT [0 - 10], [11 - 20]
FROM
(
  SELECT B.[Level], A.[Count]
    , row_number() over(partition by a.[Level]
                        order by a.[Count]) seq
  FROM [LevelTable] AS A 
  INNER JOIN (select distinct [Level] from [LevelTable]) AS B 
    ON A.[Level] = B.[Level]
) AS P
PIVOT
(
  min(P.[Count])
  for P.[Level]  in ([0 - 10], [11 - 20])
) AS PIV;

A sample for two Level I have added.you can modify the query for all the levels.

How to convert my data from row to column ?

3 Answers3