Pivot or transpose a table in SQL Server without GROUPING BY

Question

I need to pivot a table in SQL Server with the following structure:

CREATE TABLE table1 (
    ColumnNumber int,
    RowNumber int,
    CellData nvarchar(50)
)

INSERT INTO table1 VALUES
(1, 1, 'Orange'),
(2, 1, 'Apple'),
(3, 1, 'Banana'),
(1, 2, 'Grape'),
(2, 2, 'Corn'),
(3, 2, 'Lemon'),
(1, 3, 'Tomato'),
(2, 3, 'Lettuce'),
(3, 3, 'Onion')

And I need the resulting table to look like this:

enter image description here

So the cells in ColumnNumber row are now the Column Names of the resulting table. The hardest part is that the amount of different column numbers is variable (so now, we have 3 column numbers, but tomorrow there could be 6 or 10).

I've been looking at the PIVOT function, but all the examples include a GROUP BY, and, as you can see here, I need something more like a "transpose" excel function.

Thanks !!

Build the SQL dynamically. Yes, it's ugly. However, it's required because each query has a fixed shape (e.g. known column names) that is independent of the data. There are numerous examples online, search for "dynamic pivot" - e.g. http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — , Mar 06 '13 at 23:16
Also http://stackoverflow.com/questions/12430512/dynamic-pivot-table-in-sql-server?rq=1 , http://stackoverflow.com/questions/1984306/sql-server-pivot-column-data?rq=1 , etc — , Mar 06 '13 at 23:22

Taryn · Accepted Answer · 2013-03-06T23:35:34.510

This can be accomplished using the PIVOT function. The GROUP BY will work because you have an indicator that makes each of the rows distinct. For your data the indicator is the rowNumber column.

If you have a set number of columns, then you will want to hard-code them using a static pivot. The code will be similar to this following:

select [1], [2], [3]
from
(
  select colNumber, RowNumber, CellData
  from yourtable
) src
pivot
(
  max(CellData)
  for colnumber in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo.

In your case, you stated that you will have a unknown number of columns. If that is your situation then you will need to use dynamic sql to build the list of columns to pivot. I demonstrated the static version because it makes it easier to convert the code to dynamic SQL.

The key to the dynamic sql version is getting the list of columns which is done by querying your table and creating a string of the column names. This is done using FOR XML PATH:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

This list is then added into the query string that you generate and the final code is then:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
             from 
             (
                select colNumber, rowNumber, CellData
                from yourtable
            ) x
            pivot 
            (
                min(CellData)
                for colNumber in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo.

Both give the result:

|      1 |       2 |      3 |
-----------------------------
| Orange |   Apple | Banana |
|  Grape |    Corn |  Lemon |
| Tomato | Lettuce |  Onion |

@pst I'm don't understand your comment. bluefeet answered another pivot question. If you notice, they *really* seem to enjoy this category of questions and provide thorough, detailed and correct answers. — billinkc, Mar 06 '13 at 23:30
@bluefeet Although, why the choice of MIN here, vs MAX in the other? Just daily preference or does it affect the semantics to either? — , Mar 06 '13 at 23:35
@pst You could use either `min` or `max` since the aggregation is being done on a string. They will both give the same result. — Taryn, Mar 06 '13 at 23:36
Thanks a lot, this is exactly what I was looking for. Sorry if it's a repeated question, but I didn't find any other question like this one. — Alvaro VS, Mar 07 '13 at 00:31

Pivot or transpose a table in SQL Server without GROUPING BY

1 Answers1