Transposing a table in SQL server

Question

I am using SQL Server 2005 for my application. I have a table in my stored procedure which has two columns, C1 and C2. I want to transpose this table such that the values of column C1 becomes the columns. Before transpose (Table 1):

C1  C2
M1  U1
M1  U2
M1  U3
M2  U4
M2  U5

After Transpose (Table 2):

M1  M2
U1  U4
U2  U5
U3  NULL

In Table1, the number of distinct values (M1, M2) may vary. So, the columns in Table2 are not fix.

Please provide a solution to achieve the same.

Answer 1

This isnt exact result that you want but you can try this

;WITH TAB1 AS
( SELECT  [ResponsibleID] C1,[ActionID] C2,1 ORD
  FROM [TEST].[dbo].[yourtable]
  WHERE 1=1
  )


 SELECT 
    CASE WHEN M1=1 THEN ActionID ELSE NULL END M1,
    CASE WHEN M2=1 THEN ActionID ELSE NULL END M2 
 FROM TAB1
 PIVOT(AVG(ORD) FOR RESPONSIBLEID IN ([M1],[M2])) AS ABC

Answer 2

For this type of data transformation you will want to use the PIVOT function that is available in SQL Server 2005+. There are two ways to apply the pivot function.

If you know the values ahead of time, then you can hard-code the values in the query. Similar to this:

select M1, M2
from
(
  select c1, c2,
    row_number() over(partition by c1 order by c1, c2) rn
  from yourtable
) src
pivot
(
  max(c2)
  for c1 in (M1, M2)
) piv

See SQL Fiddle with Demo.

But if you have an unknown number of values that you want to transpose into columns, then you can use dynamic SQL to create the query at run-time.

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

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

set @query = 'SELECT ' + @cols + ' from 
             (
                select C1, C2,
                  row_number() over(partition by c1 order by c1, c2) rn
                from yourtable
            ) x
            pivot 
            (
                max(C2)
                for C1 in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo.

Both will give the same result, the difference is the dynamic version is flexible if the values will change:

| M1 |     M2 |
---------------
| U1 |     U4 |
| U2 |     U5 |
| U3 | (null) |

Answer 3

This is probably a case where dynamic sql is your friend. Assuming your base table's name is "Transpose":

--Get a Unique List of values from C1 --> These will be our column headers
DECLARE @Unique TABLE (C1 VARCHAR(25), fUsed BIT DEFAULT 0);
INSERT INTO @Unique (C1) SELECT DISTINCT C1 FROM Transpose;

DECLARE @TransSQL NVARCHAR(4000);
DECLARE @ColID NVARCHAR(25);

SET @TransSQL = N'SELECT ' 

--Loop over unique C1 values and construct the select statement
SELECT @ColID = (SELECT TOP 1 C1 FROM @Unique WHERE fUSed = 0);
WHILE @@ROWCOUNT <> 0 AND @ColID IS NOT NULL
BEGIN
    SET @TransSQL = @TransSQL + 'CASE C1 WHEN ' + '''' + @ColID + '''' + ' THEN C2 ELSE NULL END AS ' + @ColID + ', '

    --Update flag in table so we don't use this field again
    UPDATE u SET fUsed = 1 FROM @Unique u WHERE C1 = @ColID;
    SELECT @ColID = (SELECT TOP 1 C1 FROM @Unique WHERE fUSed = 0);
END

--Remove Trailing comma and add FROM clause
DECLARE @SQL NVARCHAR(4000)
SET @SQL = LEFT(@TransSQL,LEN(@TransSQL) -1) + ' FROM Transpose'

--For debugging purposes
PRINT @SQL;

--Execute the dynamic sql
EXEC sp_executesql @SQL;

This won't achieve the exact layout your question described because their isn't a key field to group the results by. Instead, the output will look like the following:

M1     M2      M3
U1     NULL    NULL
U2     NULL    NULL
U3     NULL    NULL
NULL   U4      NULL
NULL   U5      NULL
NULL   NULL    U6

If your base table has a key field the query can be modified slightly to group the results by the key field and probably come a bit closer to your stated goal for the resulting data.