CODEWITHSUNDEEP
sql-serversql-server-2005pivot
RKh
RKh

Reputation: 14159

Pivoting one column data to rows while keeping other columns intact

I am retrieving few records from SQL Server 2005 database:

enter image description here

I want to pivot data to show results as below:

enter image description here

The dates and processes can be unlimited so I want to use dynamic pivot. I tried few solutions but unable to make it work.

Upvotes: 0

Views: 898

Answers (1)

Lamak
Lamak

Reputation: 70678

Well, first of all, you can't have an unlimited number of columns in a table, so I'm assuming that you have less 1023 processes. Anyway, you'll need to use a dynamic pivot for this:

DECLARE @Processes AS NVARCHAR(MAX), @Query  AS NVARCHAR(MAX);

SELECT @Processes = STUFF(( SELECT DISTINCT ',' + QUOTENAME(Process) 
                            FROM YourTable
                            WHERE Process IS NOT NULL
                            FOR XML PATH(''), 
                            TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @Query = '
SELECT *
FROM YourTable T
PIVOT(SUM([Percent]) FOR Process IN ('+@Processes+')) AS PT'

EXEC(@Query)

Here is a sqlfiddle with a live demo.

Upvotes: 1

Related Questions