what would be the simpler way to accomplish this SQL row to column transpose?

Question

This is the table from a select:

+---------+-------+------------+-----------------------------+-------+
|   id    | name  |    date    |          dtinsert           | hour  |
+---------+-------+------------+-----------------------------+-------+
| 1355428 | Max   | 2019-12-16 | 2019-12-16 13:29:21.2500000 | 11:26 |
| 1355433 | Max   | 2019-12-16 | 2019-12-16 13:31:07.5133333 | 11:29 |
| 1355436 | Peter | 2019-12-16 | 2019-12-16 13:32:52.1600000 | 11:30 |
| 1355445 | Peter | 2019-12-16 | 2019-12-16 13:36:34.9466667 | 11:33 |
| 1355456 | Troy  | 2019-12-16 | 2019-12-16 13:41:22.7300000 | 11:36 |
+---------+-------+------------+-----------------------------+-------+

this is what I want

+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
|              1              |              2              |              3              |              4              |              5              |
+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
| 1355428                     | 1355433                     | 1355436                     | 1355445                     | 1355456                     |
| Max                         | Max                         | Peter                       | Peter                       | Troy                        |
| 16/12/2019                  | 16/12/2019                  | 16/12/2019                  | 16/12/2019                  | 16/12/2019                  |
| 2019-12-16 13:29:21.2500000 | 2019-12-16 13:31:07.5133333 | 2019-12-16 13:32:52.1600000 | 2019-12-16 13:36:34.9466667 | 2019-12-16 13:41:22.7300000 |
| 11:26                       | 11:29                       | 11:30                       | 11:33                       | 11:36                       |
+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+

[Sample data](https://meta.stackexchange.com/questions/81852/) is better presented as [formatted text](http://stackoverflow.com/help/formatting). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. — , Dec 27 '19 at 12:30
Will the amount of columns be dynamic or static? E.g. Will the output table always have 5 columns or will this change based on different numbers of ID's? — Atmira, Dec 27 '19 at 12:32
have a look at https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — RamPrakash, Dec 27 '19 at 12:55
SQL tables/result sets are not spreadsheets, however similarly they may be presented. Columns and Rows are *not* the same sorts of things (notably, everything in a column has to be of the same data type). It would be far better to do this transpose in a *presentation layer* (application or report builder) than to try and do it in SQL. — Damien_The_Unbeliever, Dec 27 '19 at 13:13
The numbers of columns would change based on the numbers of id returned — Luis, Dec 27 '19 at 13:47

score 0 · Answer 1 · answered Dec 27 '19 at 14:32

You would need to do a dynamic pivot-table. First you need to declare all the columns that should be created. This is done with the @Cols declaration. Then you need to make a call which contains all the columns, which is done in the @SQL declaration here. Lastly the call is executed by an EXEC. As I don't have the data due to the image, I can only explain the logic, not the actual end-result, but if you add the data as a table, I can adjust the query to that.

    DECLARE @SQL VARCHAR(1000)
    DECLARE @Cols VARCHAR(1000)

    SET @Cols = Stuff((
                SELECT DISTINCT ',' + ' ' + QuoteName(ID) + ''
                FROM [TableName] A
                ORDER BY 1
                FOR XML Path('')
                ), 1, 1, '') + ''

      --  Print @Cols

 SET @SQL = ' 

    Select
            Name
            ,' + @Cols + '
         From  [TableName] A
          Pivot (MAX([Columns]) For [ID] in ( ' + @Cols + ')
          ) as pvt
          '

        --  Print @SQL
        EXEC (@SQL)

what would be the simpler way to accomplish this SQL row to column transpose?

1 Answers1