transform table in t-sql columns to rows

Asked at 2017-01-11 14:48:28Z
  • 5 Subscribers
  • 114 Views
-2

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
...

1 answers in total

2
Prdp Posted at 2017-01-11 15:03:11Z

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) 

Answer this questsion