CODEWITHSUNDEEP
sqlsql-server
Bruno Faria
Bruno Faria

Reputation: 5272

query to transform rows into column

I need some help to transform data in this format:

Owner   WeekStart   Total
04206C26-347A-4984-BAD4-4C812D6035A4    07/07/2014  495
04206C26-347A-4984-BAD4-4C812D6035A4    14/07/2014  650
04206C26-347A-4984-BAD4-4C812D6035A4    21/07/2014  454
06454465-9E7A-4385-8B5F-3436F0B0983E    07/07/2014  160
06454465-9E7A-4385-8B5F-3436F0B0983E    14/07/2014  492
06454465-9E7A-4385-8B5F-3436F0B0983E    21/07/2014  448

Into something like this:

Owner   07/07/2014  14/07/2014  21/07/2014
04206C26-347A-4984-BAD4-4C812D6035A4    495 650 454
06454465-9E7A-4385-8B5F-3436F0B0983E    160 492 448

I'm thinking it might be possible using pivots but I failed to find a solution. I'm using SQL Server 2008.

Upvotes: 0

Views: 50

Answers (1)

Alberto Solano
Alberto Solano

Reputation: 8227

This query should solve your issue:

declare @table table(Owner uniqueidentifier, WeekStart varchar(30), Total int)

INSERT INTO @table(Owner,WeekStart,Total) VALUES( '04206C26-347A-4984-BAD4-4C812D6035A4', '07/07/2014', 495)
INSERT INTO @table(Owner,WeekStart,Total) VALUES( '04206C26-347A-4984-BAD4-4C812D6035A4', '14/07/2014', 650)
INSERT INTO @table(Owner,WeekStart,Total) VALUES( '04206C26-347A-4984-BAD4-4C812D6035A4', '21/07/2014', 454)
INSERT INTO @table(Owner,WeekStart,Total) VALUES( '06454465-9E7A-4385-8B5F-3436F0B0983E', '07/07/2014', 160)
INSERT INTO @table(Owner,WeekStart,Total) VALUES( '06454465-9E7A-4385-8B5F-3436F0B0983E', '14/07/2014', 492)
INSERT INTO @table(Owner,WeekStart,Total) VALUES( '06454465-9E7A-4385-8B5F-3436F0B0983E', '21/07/2014', 448)

select * from @table

SELECT Owner, [07/07/2014], [14/07/2014], [21/07/2014]
FROM
(SELECT Owner, WeekStart, Total 
    FROM @table) AS SourceTable
PIVOT
(
AVG(Total)
FOR WeekStart IN ([07/07/2014], [14/07/2014], [21/07/2014])
) AS PivotTable
order by Owner;

To see the execution result of this query, see this SQL Fiddle.

Upvotes: 1

Related Questions