i当前有一个表,该表在SQL Server中以季度存储信息:
CREATE TABLE [dbo].[payroll](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[fk_employer] [bigint] NULL,
[branch] [varchar](5) NULL,
[natural_year] [int] NULL,
[fiscal_year] [int] NULL,
[quarter] [varchar](2) NULL,
[payroll_total] [bigint] NULL,
[emp_total] [bigint] NULL,
[tax_total] [bigint] NULL,
[created_on] [datetime] NOT NULL,
[created_by] [varchar](50) NOT NULL,
[updated_on] [datetime] NULL,
[updated_by] [varchar](50) NULL
我想在所有4个季度中以payroll_total
逐年显示此信息,所有4个季度的emp_total
,tax_total
,在所有4个季度中。例如,结果将是:
[fk_employer], [branch], [natural_year], [payroll_total_Q1], [payroll_total_Q2], [payroll_total_Q3], [payroll_total_Q4], [emp_total_Q1], [emp_total_Q2], [emp_total_Q3], [emp_total_Q4], [tax_total_Q1], [tax_total_Q2], [tax_total_Q3], [tax_total_Q4]
最好还为会计年度创建另一种观点(在我们的情况下,第三季度,第1季度,Q2应该是同一会计年度)。
也希望它可以更新,我知道这是一个伸展的方法,但我希望有一种方法。
我不是DBA,所以我的知识是有限的,有没有办法在视图中做到这一点?
您可以使用如下
的条件聚合select
[fk_employer], [branch], [natural_year],
[payroll_total_Q1]= SUM(CASE WHEN [quarter]='Q1' THEN [payroll_total] END),
[payroll_total_Q2]= SUM(CASE WHEN [quarter]='Q2' THEN [payroll_total] END),
[payroll_total_Q3]= SUM(CASE WHEN [quarter]='Q3' THEN [payroll_total] END),
[payroll_total_Q4]= SUM(CASE WHEN [quarter]='Q4' THEN [payroll_total] END),
[emp_total_Q1]= SUM(CASE WHEN [quarter]='Q1' THEN [emp_total] END),
[emp_total_Q2]= SUM(CASE WHEN [quarter]='Q2' THEN [emp_total] END),
[emp_total_Q3]= SUM(CASE WHEN [quarter]='Q3' THEN [emp_total] END),
[emp_total_Q4]= SUM(CASE WHEN [quarter]='Q4' THEN [emp_total] END),
[tax_total_Q1]= SUM(CASE WHEN [quarter]='Q1' THEN [tax_total] END),
[tax_total_Q2]= SUM(CASE WHEN [quarter]='Q2' THEN [tax_total] END),
[tax_total_Q3]= SUM(CASE WHEN [quarter]='Q3' THEN [tax_total] END),
[tax_total_Q4]= SUM(CASE WHEN [quarter]='Q4' THEN [tax_total] END)
from payroll
group by
[fk_employer], [branch], [natural_year]
在财政年度,仅由fiscal_year
而不是natural_year
组组,将SELECT natural_year
替换为fiscal_year