transform table in t-sql columns to rows

Question

I have a table in sql with the following data:

[col1]  [col2]
1       aabb
2       ccdd
3       eeff
4       gghh
1       uuii
2       oopp
3       hhjj
4       lltt
...

The values in col1 are always known. How can I transform this in a table where the values of the first column will be the new columns like this:

[1]   [2]   [3]   [4]
aabb  ccdd  eeff  gghh
uuii  oopp  hhjj  lltt
...

I tryed using pivot but pivot requires the use of an agregator function and I don't want to aggregate any data — Gnomo, Jan 11 '17 at 14:51
@Prdp It's always a fixed number of columns but not always 4 — Gnomo, Jan 11 '17 at 14:52
I'll try to clarify. For any given table the number of columns is fixed. — Gnomo, Jan 11 '17 at 14:57
am taking about result. In result will it be always 4 columns ? — Pரதீப், Jan 11 '17 at 14:58
You can still pivot, forcing an aggregation on something like max or min. Additionally you can pivot dynamically, not knowing how many columns will be required. Both problems have been asked and answered on SO and Database Admininstrator SE sites — Steve Mangiameli, Jan 11 '17 at 14:59
I have been looking at this example http://stackoverflow.com/questions/18026236/sql-server-columns-to-rows but I wasn't able to make it work for me — Gnomo, Jan 11 '17 at 15:03
How about the values inside it will be always `1,2,3 & 4` or it can be anything — Pரதீப், Jan 11 '17 at 15:04

Pரதீப் · Accepted Answer · 2017-01-11T15:09:23.907

You need to use Row_Number and Pivot/Conditional Aggregation

If the values in col1 is always know then

;WITH cte
        AS (SELECT Row_number()OVER(partition BY [col1] ORDER BY [col2]) rn, *
            FROM   Yourtable)
SELECT  [1] = Max(CASE WHEN [col1] = 1 THEN [col2] END),
        [2] = Max(CASE WHEN [col1] = 2 THEN [col2] END),
        [3] = Max(CASE WHEN [col1] = 3 THEN [col2] END),
        [4] = Max(CASE WHEN [col1] = 4 THEN [col2] END)
FROM   cte
GROUP  BY rn

Here is a dynamic approach

DECLARE @col_list VARCHAR(8000)= '',
        @sql      VARCHAR(8000)

SELECT @col_list = (SELECT DISTINCT ',' + Quotename([col1])
                    FROM   Yourtable
                    FOR xml path (''))

SET @col_list = Stuff(@col_list, 1, 1, '')
SET @sql = 'select ' + @col_list
           + ' from (SELECT Row_number()OVER(partition BY [col1] ORDER BY [col2]) rn, *
                FROM   Yourtable) a pivot (max([col2]) for col1 in ('
           + @col_list + '))pv'

PRINT @sql

EXEC (@sql)

transform table in t-sql columns to rows

1 Answers1