Sql: row values ​​as columns

Question

I have a table where I store transactions for such contracts:

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 select the time interval of the request, what I want to archive is that if the user selects the time interval from 01/2012 to 03-2012, he gets the result:

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

Do you have any suggestions on how I can solve this?

Regards, r3try

EDIT: THANKS FOR ANSWERS SO MUCH! When I googled for my problem, I also stumbled upon a turn, but I have not yet found an example that really solves my problem (since basically each example provides specific possibilities for writing columns, but in my example it might look like "March 2012" ',' April 2012 ', ...,' January 2013 ', ...)

Just to give you guys more information on what I plan to do with the date received from the SQL query: I have an asp.net webforms website with gridview that contains some contract data ... now this table needs to be expanded due to the information on payments for this specific contract (mainly the Id_Contract connection, but "rotary"). If the user selects March 2012 - May 2012, Gridview should contain normal contract data + 3 columns for payment information (March, April, May). Only records that have already inserted a value are stored in the database. -> I hope that the explanation will simplify the situation.

Answer 1

Your problem can be solved with Dynamic Pivoting. Please read this article.

try it

 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 

output

 Id_Contract Jan 2012 Mar 2012 1 500 450 2 NULL 450 

Let me know if this meets the requirements.

Answer 2

What you want to do is called rotary, and it is supported by the built-in PIVOT and UNPIVOT relational operators. .

I will analyze the answer using the solution, but for now, take a look at the link, you will get a general idea.