想要在SQL中显示数据透视表 [英] Want to display pivot Table in SQL
问题描述
我有2张桌子(账户树,透明记录)
账户树(代码,账户名称)
每日记录(日期,<代码,帐户>
我想要显示这个
月份代码账户信用卡借记
--------------------------------------- ------------------
Jan
Feb
Mar
ETC ..
假设你有一个如此定义的表格
CREATE TABLE [dbo]。 [testpivot](< br />
[月] [varchar](50)NOT NULL,< br />
[销售] [钱] NOT NULL,< br />
[InsertDate] [date] NULL< br />
)ON [PRIMARY]
你有以下内容行:
月销售额InsertDate
2015年1月20日2015-01-01
2015年2月15日2015- 01
2015年3月18日2015-01-01
2015年4月19日2015-01-01
2015年5月14日2015-01-01
Jun 21.00 2015-01-01
您可以转向以下内容:
JanSales,FebSales,MarSales,AprSales
20.00,15.00,18.00,19.00
使用以下两种方法。
1。自我加入
选择 j.Sales as JanSales,f.Sales as FebSales,m.Sales as MarchSales ,a.Sales as AprilSales 来自 dbo.testpivot j
关键词> join dbo.testpivot f f.InsertDate = j.InsertDate 和 f 。[月] = ' 2月'
dbo.testpivot m on m.InsertDate = j.InsertDate 和 m。[月] = ' Mar'
< span class =code-keyword> join dbo.testpivot a on a.InsertDate = j.InsertDate 和 a。[月] = ' 4月'
其中 j。[MONTH] = ' Jan'
2.透视关键字。
选择 Jan as JanSales,Feb as FebSales,Mar as MarchSales,Apr as AprilSales
< span class =code-keyword> from dbo.testpivot
pivot
(
max(Sales)
for [Month] IN (1月,2月,3月,4月)
) as pvt
I have 2 tables (Account Tree , Dialy Records)
Account tree (Code,Account Name)
Daily Records (Date,<code,account>
I want to show this
Month Code Account Credit Debit
---------------------------------------------------------
Jan
Feb
Mar
ETC..
Assuming you have a table defined as such
CREATE TABLE [dbo].[testpivot](<br /> [Month] [varchar](50) NOT NULL,<br /> [Sales] [money] NOT NULL,<br /> [InsertDate] [date] NULL<br /> ) ON [PRIMARY]
and you have the following rows:
Month Sales InsertDate
Jan 20.00 2015-01-01
Feb 15.00 2015-01-01
Mar 18.00 2015-01-01
Apr 19.00 2015-01-01
May 14.00 2015-01-01
Jun 21.00 2015-01-01
You can pivot for the following:
JanSales, FebSales,MarSales,AprSales
20.00, 15.00, 18.00, 19.00
Using the following two methods.
1. Self Joins
select j.Sales as JanSales, f.Sales as FebSales, m.Sales as MarchSales, a.Sales as AprilSales from dbo.testpivot j join dbo.testpivot f on f.InsertDate = j.InsertDate and f.[Month] = 'Feb' join dbo.testpivot m on m.InsertDate = j.InsertDate and m.[Month] = 'Mar' join dbo.testpivot a on a.InsertDate = j.InsertDate and a.[Month] = 'Apr' where j.[MONTH] = 'Jan'
2. Pivot Key word.
select Jan as JanSales, Feb as FebSales, Mar as MarchSales, Apr as AprilSales from dbo.testpivot pivot ( max(Sales) for [Month] IN (Jan, Feb, Mar, Apr) ) as pvt
这篇关于想要在SQL中显示数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!