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 上的一个连接，但'pivoted').如果用户选择 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屋！