PIVOT in SQL Table

Question

I have a table like this:

Value            Name

ProjectA       ProjectName
10/10/2012     StartDate
10/10/2013     EndDate
ProjectB       ProjectName
11/11/2012     StartDate
11/11/2013     EndDate
ProjectC       ProjectName
12/12/2012     StartDate
12/12/2013     EndDate

I need to change the table into:

Project Name           Start Date           End Date

ProjectA               10/10/2012          10/10/2013 
ProjectB               11/11/2012          11/11/2013 
ProjectC               12/12/2012          12/12/2013

I have used the following query to achieve my result,

select * from Project 
pivot
(
max(Value)
for[Name] in ([Project Name],[Start Date],[End Date])
)piv

Once i Execute this I am just getting the result as:

 Project Name     Start Date      End Date

    Project C           12/12/2012  12/12/2013

What mistake have i done in my query?

Peter_R · Accepted Answer · 2013-10-17 09:41:49Z

The problem relates to the Max(Value) it will only return the highest value, Pivot is not really designed to do non aggregation values.

This should however solve your issue.

    WITH CTE(Value, Name,RID)
    AS
    (
    SELECT Value, Name, ROW_NUMBER() OVER (PARTITION BY (Name)ORDER BY Value) AS RID FROM  YOURTABLE --Replace 
    )
    SELECT [ProjectName],[StartDate],[EndDate]
    FROM
    (SELECT Value,Name,RID
    FROM CTE)C
    PIVOT
    (
    max(Value)
    FOR Name IN ([ProjectName],[StartDate],[EndDate])
    ) AS PivotTable;

It worked on my instance here with your test data.

Edit: Dynamic Request

I am not that good with Dynamic SQL and I am unable to get the columns to order correctly, although this is just due to the way it groups the column names in desc order.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N'' + QUOTENAME(Name) + ', '
    FROM TestTable
    GROUP BY Name

Set @columns = LEFT(@Columns,Len(@Columns) -1)
Print @columns

SET @sql = N'
WITH CTE(Value, Name,RID)
AS
(
SELECT Value, Name, ROW_NUMBER() OVER (PARTITION BY (Name)  ORDER BY Value) AS RID FROM TestTable
)   
SELECT  ' + @columns + '
FROM
(SELECT Value,Name,RID
FROM CTE)C
PIVOT
(
max(Value)
FOR Name IN (' + @columns + ' )
) AS PivotTable;';
PRINT @sql;
EXEC sp_executesql @sql;

Collectives™ on Stack Overflow

PIVOT in SQL Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related