sql: values of rows as columns

Question

I have got a table where i store transactions for contracts like so:

Id_Contract[int] | Month[DateTime] | Amount[int]
------------------------------------------------
        1          2012-01-01 00:00:00.000      500     
        1          2012-03-01 00:00:00.000      450 
        2          2012-09-01 00:00:00.000      300     
        3          2012-08-01 00:00:00.000      750 

The user should be able to chose the timespan of the query what i want to archive is that if the user chooses a timespan from 01/2012 - 03-2012 he gets the result:

Id_Contract[int] | Jan 2012 | Feb 2012 | Mar 2012
--------------------------------------------------
    1                   500                       450

Do you have any suggestions how i could solve this?

Best regards, r3try

EDIT: THANKS FOR THE ANSWERS SO FAR! When i googled for my problem i also stumbled across pivoting, but i havent found an example so far that really solves my issue (because basically every example gives the specific possibilities for the column entries, but in my example it can be like 'March 2012', 'April 2012', ..., 'January 2013', ...)

Just to give you guys some more background info on what im planning to do with the date i got from the sql query: I have got an asp.net webforms site with a gridview that contains some data on contracts... now this table should be expanded by the payment information on that specific contract (basically a join on Id_Contract but 'pivoted'). If the user chooses March 2012 - May 2012 the Gridview should contain the normal data on the contract + 3 columns on payment information (March, April, May). In the DB there are only entries stored which have already an inserted value. -> i hope that explanation makes things a little bit clearer.

Answer 1

Your problem can be solve using Dynamic Pivoting. Please look into this article

Try this

DECLARE @t TABLE(Id_Contract INT, Dt DATETIME,Amount INT)
INSERT INTO @t SELECT 1,'2012-01-01 00:00:00.000',500
INSERT INTO @t SELECT 1,'2012-03-01 00:00:00.000',450
INSERT INTO @t SELECT 2,'2012-09-01 00:00:00.000',300
INSERT INTO @t SELECT 3,'2012-08-01 00:00:00.000',750

DECLARE @cols AS VARCHAR(MAX), @query  AS VARCHAR(MAX);

SELECT 
    Id_Contract
    , LEFT(DATENAME(month,Dt),3) + ' ' + DATENAME(Year,Dt) AS Month_Year_Name
    ,Amount
INTO #Temp
FROM @t 
WHERE Dt BETWEEN  '01/01/2012' AND '03/31/2012'

SELECT  @cols = STUFF(( SELECT DISTINCT 
                               '],[' +   t2.Month_Year_Name
                        FROM    #Temp AS t2
                        ORDER BY '],[' + t2.Month_Year_Name
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

SET @query = 'SELECT Id_Contract, ' + @cols + ' FROM 
            (
                SELECT
                     Id_Contract
                    , Amount
                    , Month_Year_Name
                FROM #Temp
           ) x
            PIVOT 
            (
                 MAX(amount)
                 FOR Month_Year_Name in (' + @cols + ')
            ) p '
EXECUTE(@query)
DROP TABLE #Temp

// Result

Id_Contract Jan 2012    Mar 2012
1           500         450

Edit

For your test data,

DECLARE @t TABLE(Id_Contract INT, Dt DATETIME,Amount INT) 
INSERT INTO @t SELECT 1,'2012-01-01 00:00:00.000',500 
INSERT INTO @t SELECT 1,'2012-03-01 00:00:00.000',450 
INSERT INTO @t SELECT 2,'2012-03-01 00:00:00.000',450 
INSERT INTO @t SELECT 3,'2012-08-01 00:00:00.000',750 

the output is

Id_Contract Jan 2012    Mar 2012
1              500          450
2              NULL         450

Let me know if it satisfies the requirement.

Answer 2

What you want to do is called pivoting, and it's supported by the build-in PIVOT and UNPIVOT relational operators..

I'll expand the answer with a solution, but for now, take a look at the link, you'll get the general idea.