sql:将行的值作为列 [英] sql: values of rows as columns
问题描述
我有一张桌子，我在其中存储合同的交易，如下所示:
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
用户应该能够选择查询的时间跨度 我要存档的是，如果用户从01/2012-03-2012中选择一个时间跨度，他会得到结果:
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?
最诚挚的问候， r3try
Best regards, r3try
非常感谢您的答复！ 当我搜寻我的问题时，我也偶然发现了旋转问题，但是到目前为止，我还没有找到一个可以真正解决我问题的示例(因为基本上每个示例都为列条目提供了特定的可能性，但是在我的示例中，它可能像是'2012年3月'，'2012年4月'，...，'2013年1月'，...)
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', ...)
只是给你们一些有关我打算如何处理我从sql查询中获取的日期的背景信息: 我有一个带有Gridview的asp.net Webforms网站，其中包含一些合同数据...现在，该表应通过该特定合同的付款信息进行扩展(基本上是Id_Contract上的联接，但已透视"). 如果用户选择2012年3月至2012年5月，则Gridview应包含合同上的常规数据+ 3列付款信息列(3月，4月，5月). 在数据库中，仅存储已具有插入值的条目. ->我希望解释能使事情变得更清楚.
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.
推荐答案
Your problem can be solve using Dynamic Pivoting. Please look into this article
尝试一下
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
//结果
Id_Contract Jan 2012 Mar 2012
1 500 450
修改
对于您的测试数据，
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
输出为
Id_Contract Jan 2012 Mar 2012
1 500 450
2 NULL 450
让我知道它是否满足要求.
Let me know if it satisfies the requirement.
这篇关于sql:将行的值作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
