April 28, 2010 at 2:22 am
Hello
Can you help me ?
I have table like this:
Years Amount
2006 56
2007 67
2008 10
2009 45
I need swap rows to column and result must be:
2006 2007 2008 2009
56 67 10 45
Any idea ?
Tahnk you
April 28, 2010 at 2:47 am
Yana, here is the code for you.. But i recommend yout to provide your full table definitions etc etc so that we can give u the best possible code..
IF OBJECT_ID('TEMPDB..#SomeTable1') IS NOT NULL
DROP TABLE #SomeTable1
CREATE TABLE #SomeTable1
(
[Year] SMALLINT,
Amount INT
)
GO
INSERT INTO #SomeTable1
([Year], Amount)
SELECT 2007, 1 UNION ALL
SELECT 2008, 2 UNION ALL
SELECT 2009, 3 UNION ALL
SELECT 2010, 4
SELECT
SUM(CASE WHEN [Year] = 2007 THEN Amount ELSE 0 END) AS [2007],
SUM(CASE WHEN [Year] = 2008 THEN Amount ELSE 0 END) AS [2008],
SUM(CASE WHEN [Year] = 2009 THEN Amount ELSE 0 END) AS [2009],
SUM(CASE WHEN [Year] = 2010 THEN Amount ELSE 0 END) AS [2010]
FROM #SomeTable1
Hope this helps..
Cheers!
April 28, 2010 at 4:31 am
Thank you for your answer.
I need to create report. User choose between wich years he want to see the report.
for example beetween 2004 and 2009.
My steps :
CREATE TABLE TempTable([year] int ,amount bigint)
while @year_from < @year_to+1
begin
exec @amount = [dbo].[CalculateAmount] ,@year_from
insert into TempTable ([year],amount) values(@year_from,@amount )
end
April 28, 2010 at 4:33 am
befor end while: set @year_from =@year_from +1
now i have thia table:
year amount
2004 4
2005 5
2006 6
2007 7
2008 8
but the customer whant to see:
2004 2005 2006 2007 2008
4 7 6 7 8
what the best way to do this ?
Thank you
April 28, 2010 at 5:24 am
Yana, you will have to use Dynamic SQL if you are unsure of the start and end dates.. Here is that piece of code..
SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#SomeTable1') IS NOT NULL
DROP TABLE #SomeTable1
CREATE TABLE #SomeTable1
(
[Year] SMALLINT,
Amount INT
)
GO
INSERT INTO #SomeTable1
([Year], Amount)
SELECT 2001, 1 UNION ALL
SELECT 2002, 2 UNION ALL
SELECT 2003, 3 UNION ALL
SELECT 2004, 4 UNION ALL
SELECT 2005, 5 UNION ALL
SELECT 2006, 6 UNION ALL
SELECT 2007, 7 UNION ALL
SELECT 2008, 8 UNION ALL
SELECT 2009, 9 UNION ALL
SELECT 2010, 10
--====== SPECIFY YOUR START AND END YEAR HERE
DECLARE @START_YEAR INT, @END_YEAR INT
SET @START_YEAR = 2001
SET @END_YEAR = 2010
DECLARE @Col_List VARCHAR(2000)
SELECT @Col_List = ''
SELECT
@Col_List = @Col_List + ' ['+CAST( [Year] AS VARCHAR) + '] ,'
FROM #SomeTable1
WHERE
[Year] BETWEEN @START_YEAR AND @END_YEAR
SELECT @Col_List = LEFT(@Col_List , DATALENGTH(@Col_List) -1)
DECLARE @PIVOT_QUERY VARCHAR(2000)
SELECT @PIVOT_QUERY = '
SELECT '+@Col_List +' FROM
(SELECT 1 N , [Year] , [Amount] FROM #SomeTable1) PIVOT_TABLE
PIVOT
(MAX([Amount]) FOR [Year] IN ('+@Col_List+') ) PIVOT_HANDLE'
SELECT @PIVOT_QUERY
--EXEC (@PIVOT_QUERY )
Tell me if it helped you!
Cheers!
April 28, 2010 at 6:16 am
Are you using a reporting engine for this? Most, if not all of them, will handle that quite easily for you... such as a matrix in Sql Server Reporting Services or even basic pivot table in excel.
April 28, 2010 at 7:00 am
THANK YOU A LOT !!!!!
I did not use Pivot .....
April 28, 2010 at 8:42 am
To learn how to do this in the future, please see the following article...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply