2

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.

Taryn
  • 242,637
  • 56
  • 362
  • 405
arpit agarwal
  • 21
  • 1
  • 3

3 Answers3

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) |
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • nice solution, but can you elaborate on what `row_number() over(partition by c1 order by c1, c2) rn` does in this case? – Jeremy S. Mar 21 '13 at 08:04
  • @JeremyS. When you are working with string values your options for aggregation are limited to `max` or `min`. As a result, if you do not use a `row_number` you will wind up with only one row. The `row_number` with the `partition` generates a distinct value that keeps the values separated during the `group by`. See [this demo](http://sqlfiddle.com/#!3/33fbb/21) to see what happens without the `row_number`, you only get one row. – Taryn Mar 21 '13 at 09:52
  • thanks for the example, I saw that and still don't understand how MSSQL knows to consider ``row_number`` as a distinct value, it isn't included in the group by clause cause there is none, how come MSSQL knows that? – Jeremy S. Apr 01 '13 at 16:22
  • @JeremyS. The `PIVOT` is performing an aggregation and while the `group by` is not explicitly stated it is being used. I suggest reading the [PIVOT docs](http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) on MSDN. Or if you have a specific question, then post a new question. :) – Taryn Apr 01 '13 at 16:27
  • Hello there, your transponation is very useful for me, but I can't use it on mysql server 5.1. I get an error regarding the "declare" thing at the beginning. What can I do to make this run? – LStrike Apr 04 '13 at 12:09
  • @LStrike MySQL has different syntax to pivot data. Can you post a new question and then post a link here and I will take a look? – Taryn Apr 04 '13 at 12:10
  • @bluefeet Here it is: http://stackoverflow.com/questions/15810987/using-declare-in-mysql-5-1 – LStrike Apr 04 '13 at 12:22
0

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.

Tim Lentine
  • 7,782
  • 5
  • 35
  • 40
  • Hey thanks for your reply....is there any way to sort only a coulmn in SQL such that all other columns doesn't get sorted as a result. In excel, we can do this. Dont know about SQL Server. Please help if there is any way to do this. Thanks in advance. – arpit agarwal Dec 09 '11 at 05:16
0

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
gngolakia
  • 2,176
  • 1
  • 18
  • 13