March 11, 2011 at 4:48 am
Hi,
I need to convert rows into columns, so i can compute average of this values,
like in this example:
I have a query that returns this:
col1 col2 col3
3 2 5
And i want to convert this at:
col
3
2
5
Anyone has any idea how to do this?
Thanks in advance.
March 11, 2011 at 4:56 am
Simple pivot example.
DECLARE @table AS TABLE(Id INT, Product CHAR(5), TT INT, Week CHAR(2))
INSERT INTO @table
SELECT 1, 'Test1', 75, '1'
UNION ALL SELECT 2, 'Test2', 20, '1'
UNION ALL SELECT 3, 'Test1', 60, '2'
UNION ALL SELECT 4, 'Test2', 40, '2'
UNION ALL SELECT 5, 'Test1', 35, '3'
UNION ALL SELECT 6, 'Test2', 65, '3'
SELECT pivoteddata.*
FROM (SELECT product, tt, week
FROM @table) AS rawdata
PIVOT (MAX(tt) FOR week IN ([1], [2], [3])) AS pivoteddata
Also, make sure to read Jeff's articles: -
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
March 11, 2011 at 6:00 am
Thanks! 😉
March 11, 2011 at 7:14 am
For more complex pivoting (tracking multiple columns, dynamic), or for even a way that is normally a better performer, check out the two articles in my signature on Cross-Tabs / Pivot, Parts 1 and 2.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2011 at 9:13 am
WayneS (3/11/2011)
For more complex pivoting (tracking multiple columns, dynamic), or for even a way that is normally a better performer, check out the two articles in my signature on Cross-Tabs / Pivot, Parts 1 and 2.
Pretty sure those are Jeff's articles that I posted in my reply 😛
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply