将具有透视结构的SQL查询从一个表复制到另一个表

Question

我是一名SQL初学者，尝试在TABLE_2中复制下面相同的SQL查询，类似于已经在TABLE_1中成功实现的查询。

基本上，这两个表之间的主要区别在于TABLE_；2021"；另一个名为"month_dt_emission"，包含月份格式引用，如1、2、3到12。

但TABLE_2包含日期格式为YYYY-MM-DD的"dt_emission"列，该列已经包含了我需要的嵌入年份和月份。

带列参考的表格：

• SERVER DB TABLE_1 w/列名-->年排放量，月排放量，客户id，品牌名称

• SERVER DB TABLE_2 w/列名-->dt_emission，customer_id，brand_name

我已经尝试过使用YEAR和MONTH等函数分别捕获/转换年份号和月份号，但没有成功。

以下代码是TABLE_1中应用的原始代码。有人能帮助和指导我使用适当的查询来修复和使用TABLE_2引用吗？

select brand_name
, year_dt_emission
, month_dt_emission as month_order
, case month_dt_emission
when 1 then 'Jan'when 2  then 'Feb'when 3  then 'Mar'when 4  then 'Apr'when 5  then 'May'when 6  then 'Jun'
when 7  then 'Jul'when 8  then 'Aug'when 9  then 'Set'when 10 then  'Oct'when 11 then  'Nov'when 12 then  'Dec'
end as month_dt_emission
, [1] as 'Jan', [2] as 'Feb', [3] as 'Mar', [4] as 'Apr', [5] as 'May', [6] as 'Jun'
, [7] as 'Jul', [8] as 'Aug', [9] as 'Set', [10] as 'Oct', [11] as 'Nov', [12] as 'Dec'

from (
select c.brand_name
, j_month.Joining_month
, c.month_dt_emission
, c.year_dt_emission
, count (distinct c.invoice_number) customer_id
from   TABLE_1 c
left  join (
select year_dt_emission, brand_name, customer_id, min(month_dt_emission) Joining_month
from   TABLE_1
where  1=1
group by year_dt_emission, brand_name, customer_id) j_month
on (c.customer_id = j_month.customer_id and c.brand_name = j_month.brand_name and c.year_dt_emission = j_month.year_dt_emission)
left join  (
select year_dt_emission, brand_name, customer_id, count(distinct month_dt_emission) Months_Active
from   TABLE_1
where  1=1

group by year_dt_emission, brand_name, customer_id) m_month

on (c.customer_id = m_month.customer_id and c.brand_name = m_month.brand_name and c.year_dt_emission = m_month.year_dt_emission)
where 1=1
and (m_month.Months_Active > 0 or j_month.Joining_month > 0)

group by c.brand_name
, j_month.Joining_month
, c.month_dt_emission
, c.year_dt_emission

) a
PIVOT 
(
sum(customer_id) FOR Joining_month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],[11],[12])
) AS tbpivot
´´´

Answer 1

提问时，尽量使用最小、可复制的示例。没有表定义和示例数据，我们无法运行您的查询。这使得验证/实现预期结果和解决方案变得更加困难。那些where 1=1子句似乎也没有必要。

公共表表达式(CTE(将是在数据透视之前转换数据的一种解决方案。

样本数据

-- separate columns for year and month
create table table_1
(
dt_year int,
dt_month int,
value int
);
insert into table_1 (dt_year, dt_month, value) values
(2021, 1, 100),
(2021, 1, 100),
(2021, 2, 50),
(2021, 2, 200),
(2021, 3, 70),
(2021, 4, 300),
(2022, 1, 50),
(2022, 3, 650);
-- no separate columns for year and month
create table table_2
(
dt_full date,
value int
);
insert into table_2 (dt_full, value) values
('2021-01-01', 100),
('2021-01-01', 100),
('2021-02-01', 50),
('2021-02-01', 200),
('2021-03-01', 70),
('2021-04-01', 300),
('2022-01-01', 50),
('2022-03-01', 650);

解决方案

基础数据透视查询。

select piv.dt_year,
piv.[1],
piv.[2],
piv.[3],
piv.[4],
piv.[5]
from table_1 t1
pivot (sum(t1.value) for t1.dt_month in ([1],[2],[3],[4],[5])) piv;

使用CTE扩展查询以拆分日期字段。

with cte as
(
select year(t2.dt_full) as dt_year,
month(t2.dt_full) as dt_month,
t2.value
from table_2 t2
)
select piv.dt_year,
piv.[1],
piv.[2],
piv.[3],
piv.[4],
piv.[5]
from cte
pivot (sum(cte.value) for cte.dt_month in ([1],[2],[3],[4],[5])) piv;

结果

两个示例表每个月都包含相同的值，因此输出也是相同的。

dt_year  1     2     3     4      5
-------  ----  ----  ----  -----  ----
2021     200   250   70    300    null
2022     50    null  650   null   null

坐立不安地看事情的进展。