创建sql表条目，日期为每个日期的列 [英] create sql table entries with date as columns per date

问题描述

大家好，



我怎样设法做到这一点，

它需要显示我的sql条目是按日期在一列中，这意味着每个输入日期都将成为一个列并成为一个报告。

Hi all,

How can i manage to do this,
it would require to show my sql entries to be per date in a column, so it means that every entry date would become a column and be a report.

SELECT remittance_no, remit_date, messenger, item, item_value, rate.product, rate.product_value
FROM remittance
INNER JOIN rate
ON rate.product=remittance.item

样本输出：



remittance_no remit_date messenger item item_value product product_value

REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.85

REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.75

REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.65

REMMKT00003 12/4/2015 MESSE NGER 3 PRODX 150 PRODX 1.5

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.65

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.5

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.65

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.5

REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.65

REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.65





每个相同日期应该有一列o utput，例如：产品| day1 | day2 |第3天| day4 | day5



非常感谢你，



raz

SAMPLE OUTPUT:

remittance_no remit_date messenger item item_value product product_value
REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.85
REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.75
REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.65
REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.5
REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.85
REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.75
REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.65
REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.5
REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.85
REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.75
REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.65
REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.5
REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.85
REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.75
REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.65
REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.85
REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.75
REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.65


each same date should have a single column output, ex: product | day1 | day2 | day3 | day4 | day5

Thank you so much,

raz

推荐答案

正如Tomas Takac所说，你需要了解PIVOT。除了他提供的链接之外，CodeProject还有几篇文章。



以下是一个示例数据如何转动

As Tomas Takac says, you need to know about PIVOT. As well as the link he has provided there are several articles here on CodeProject.

Here is an example of how your sample data could be pivoted

select remittance_no, messenger, item, item_value, product
	, isnull([2015-12-04],0) as day1
	, isnull([2015-12-05],0) as day2
	, isnull([2015-12-06],0) as day3
	, isnull([2015-12-07],0) as day4
	, isnull([2015-12-08],0) as day5
from
(
	select remittance_no, remit_date, messenger, item, item_value, rate.product, rate.product_value
	FROM remittance
	INNER JOIN rate
	ON rate.product=remittance.item
) as sourcedata
PIVOT
(
	sum(product_value) for remit_date in ([2015-12-04], [2015-12-05], [2015-12-06],[2015-12-07],[2015-12-08])
)  pvt

提供结果

REMMKT00003 MESSENGER 3	PRODX	150	PRODX	6.75	0	0	0	0
REMMKT00004 MESSENGER 3	PRODX	7	PRODX	0	6.75	6.75	5.25	5.25

尽管如此，您很可能不会知道最终结果中需要哪些日期。在这种情况下，您需要了解Dynamic Pivoting。这个发布 [ ^ ]包含了Itzik Ben-Gan动态构建枢轴列列表的方法示例。







CodeProject上有很多文章可以指导您连接到MySQL - 例如使用C＃和.NET连接MySQL数据库 [ ^ ]



要使用DateTimePicker构建新查询，可以执行与后续代码类似的操作。我假设您想要dtp中日期前14天的数据加上dtp上的日期。

It is highly likely though, that you are not going to know which dates are required in the final result. In which case you need to know about Dynamic Pivoting. This post[^] includes an example of Itzik Ben-Gan's approach to building the pivot column list dynamically.



There are lots of articles here on CodeProject that can guide you through connecting to MySQL - e.g. Connecting to MySQL Database using C# and .NET[^]

To get your new query built using a DateTimePicker you can do something similar to the code that follows. I've assumed that you want the data for the 14 days preceding the date in the dtp plus the date on the dtp.

Dim sCommand As MySqlCommand = New MySqlCommand()
Dim sqlQuery As StringBuilder = New StringBuilder()

sqlQuery.Append("select MSGR,PRODNAME,RteVal,")

'Set up the start date for 14 days prior to the datepicker (i.e. datepicker date is 15th date)
Dim CalcDate As Date = DateTimePicker1.Value.Date.AddDays(-14)
For i As Integer = 1 To 15
    sqlQuery.Append(String.Format("sum(case when rmdte = @Date{0} then ProdVal else 0 end) `DAY {1}`,", 15 - i + 1, i))

    sCommand.Parameters.AddWithValue(String.Format("@Date{0}", i), CalcDate)
    CalcDate = CalcDate.AddDays(1)
Next

sqlQuery.Append("sum(ProdVal)*rteval as Total from")
sqlQuery.Append("select date(remit_date) as rmdte, messenger as Msgr, item as ProdName, item_value as ProdVal,")
sqlQuery.Append("b.product as RteProd,b.product_value as Rteval FROM remittance a JOIN rate b on a.item=b.product")
sqlQuery.Append("where a.messenger = 'MESSENGER 3' and b.ratecode = '5i.35' ) as SOURCEDATA group by ProdName")

sCommand.CommandText = sqlQuery.ToString()

这篇关于创建sql表条目，日期为每个日期的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！