时间戳

首页 > 解决方案 > 如何把一张宽桌子变成一张长桌子

sql - 如何把一张宽桌子变成一张长桌子

问题描述

我有一张看起来像这样的宽桌子：

案例参考 OUTCOME_EMP_SITUATION MONTH1_EMP_SITUATION MONTH1_REASON MONTH3_EMP_SITUATION MONTH3_REASON MONTH6_EMP_SITUATION MONTH6_REASON
12345 受雇 受雇 1个月的结果 受雇 3个月的结果 受雇 6个月的结果

这是人们在完成就业计划后完成的调查结果。他们完成了 4 次调查，一次是在项目结束后立即完成，然后是 1/3/6 个月后。问题是，程序完成后立即的结果在一个表中（结果表），而 1/3/6 个月的检查点结果在另一个表中（Checkpointinfo 表）我想将这些表组合起来创建一个长表所以我将把它放在一列中，而不是在 5 个不同的列中显示“结果”，它看起来像这样：

案例参考 结果_emp_situation Month_Reason
12345 受雇 无效的
12345 受雇 1个月的结果
12345 受雇 3个月的结果
12345 受雇 6个月的结果

我想知道是否有人可以帮我把这个广泛的查询变成一个长表查询。

这是宽表的查询：

Select                                      
ch.CASEREFERENCE, oc.OUTCOME_DATE, oc.OUTCOME_REFERENCE_ID, oc.OUTCOME_EMP_SITUATION, oc.OUTCOME_EMPLOYMENT_TYPE, oc.OUTCOME_NUM_JOBS, oc.OUTCOME_NAICS_DESC, oc.OUTCOME_JOB_NATURE,                                        
oc.OUTCOME_WORK_HOURS, oc.OUTCOME_WAGE, oc.OUTCOME_STUDENT_STATUS, oc.OUTCOME_GOT_SERVICE,  oc.OUTCOME_RIGHT_SERVICE, oc.OUTCOME_RECOMMEND_PROGRAM,                                         
ck1.REASONCODE AS REASONCODE1,                                      
CASE WHEN ck1.REASONCODE = 'OT1' THEN "Outcome at 1 month" END MONTH1_REASON,                                       
ck1.MONTH_START_DATE AS MONTH1_START_DATE, ck1.MONTH_END_DATE AS MONTH1_END_DATE, ck1.MONTH_OUTCOME_EMP_SITUATION AS MONTH1_OUTCOME_EMP_SITUATION,                                      
ck1.MONTH_EMPLOYMENT_TYPE AS MONTH1_EMPLOYMENT_TYPE, ck1.MONTH_NUM_JOBS AS ,MONTH1_NUM_JOBS, ck1.MONTH_NAICS_DESC AS MONTH1_NAICS_DESC, ck1.MONTH_JOB_NATURE AS MONTH1_JOB_NATURE,                                      
ck1.MONTH_WORK_HOURS AS MONTH1_WORK_HOURS, ck1.MONTH_WAGE AS MONTH1_WAGE, ck1.MONTH_STUDENT_STATUS AS MONTH1_STUDENT_STATUS, ck1.MONTH_GOT_SERVICE AS MONTH1_GOT_SERVICE,                                       
ck1.MONTH_RIGHT_SERVICE AS MONTH1_RIGHT_SERVICE, ck1.MONTH_RECOMMEND_PROGRAM AS MONTH1_RECOMMEND_PROGRAM, ck1.MONTH_RESUBMIT_MILESTONE AS MONTH1_RESUBMIT_MILESTONE,                                        
ck1.MONTH_MILESTONE_ACHIEVED AS MONTH1_MILESTONE_ACHIEVED, ck1.MONTH_APPROVED_DATE AS MONTH1_APPROVED_DATE,                                     
ck3.REASONCODE AS REASONCODE3,                                      
CASE WHEN ck3.REASONCODE = 'OT3' THEN "Outcome at 3 month" END MONTH3_REASON,                                       
ck3.MONTH_START_DATE AS MONTH3_START_DATE, ck3.MONTH_END_DATE AS MONTH3_END_DATE, ck3.MONTH_OUTCOME_EMP_SITUATION AS MONTH3_OUTCOME_EMP_SITUATION,                                      
ck3.MONTH_EMPLOYMENT_TYPE AS MONTH3_EMPLOYMENT_TYPE, ck3.MONTH_NUM_JOBS AS ,MONTH3_NUM_JOBS, ck3.MONTH_NAICS_DESC AS MONTH3_NAICS_DESC, ck3.MONTH_JOB_NATURE AS MONTH3_JOB_NATURE,                                      
ck3.MONTH_WORK_HOURS AS MONTH3_WORK_HOURS, ck3.MONTH_WAGE AS MONTH3_WAGE, ck3.MONTH_STUDENT_STATUS AS MONTH3_STUDENT_STATUS, ck3.MONTH_GOT_SERVICE AS MONTH3_GOT_SERVICE,                                       
ck3.MONTH_RIGHT_SERVICE AS MONTH3_RIGHT_SERVICE, ck3.MONTH_RECOMMEND_PROGRAM AS MONTH3_RECOMMEND_PROGRAM, ck3.MONTH_RESUBMIT_MILESTONE AS MONTH3_RESUBMIT_MILESTONE,                                        
ck3.MONTH_MILESTONE_ACHIEVED AS MONTH3_MILESTONE_ACHIEVED, ck3.MONTH_APPROVED_DATE AS MONTH3_APPROVED_DATE,                                     
ck6.REASONCODE AS REASONCODE6,                                      
CASE WHEN ck6.REASONCODE = 'OT6' THEN "Outcome at 6 month" END MONTH6_REASON,                                       
ck6.MONTH_START_DATE AS MONTH6_START_DATE, ck6.MONTH_END_DATE AS MONTH6_END_DATE, ck6.MONTH_OUTCOME_EMP_SITUATION AS MONTH6_OUTCOME_EMP_SITUATION,                                      
ck6.MONTH_EMPLOYMENT_TYPE AS MONTH6_EMPLOYMENT_TYPE, ck6.MONTH_NUM_JOBS AS ,MONTH6_NUM_JOBS, ck6.MONTH_NAICS_DESC AS MONTH6_NAICS_DESC, ck6.MONTH_JOB_NATURE AS MONTH6_JOB_NATURE,                                      
ck6.MONTH_WORK_HOURS AS MONTH6_WORK_HOURS, ck6.MONTH_WAGE AS MONTH6_WAGE, ck6.MONTH_STUDENT_STATUS AS MONTH6_STUDENT_STATUS, ck6.MONTH_GOT_SERVICE AS MONTH6_GOT_SERVICE,                                       
ck6.MONTH_RIGHT_SERVICE AS MONTH6_RIGHT_SERVICE, ck6.MONTH_RECOMMEND_PROGRAM AS MONTH6_RECOMMEND_PROGRAM, ck6.MONTH_RESUBMIT_MILESTONE AS MONTH6_RESUBMIT_MILESTONE,                                        
ck6.MONTH_MILESTONE_ACHIEVED AS MONTH6_MILESTONE_ACHIEVED, ck6.MONTH_APPROVED_DATE AS MONTH6_APPROVED_DATE                                      
FROM PROGRAM as pg                                      
LEFT JOIN CASEINFO as ch ON pg.CASEID = ch.CASEID                                       
LEFT JOIN OUTCOME as oc ON pg.CASEID = oc.CASEID                                        
LEFT JOIN ( SELECT cp.CASEID, cp.REASONCODE, cp.MONTH_OUTCOME_EMP_SITUATION, cpi.*  FROM CHECKPOINT cp LEFT JOIN CHECKPOINTINFO cpi ON cp.CASEREVIEWID = cpi.CASEREVIEWID WHERE cpi.REASONCODE = 'OT1')ck1 ON pg.CASEID = ck1.CASEID                                        
LEFT JOIN ( SELECT cp.CASEID, cp.REASONCODE, cp.MONTH_OUTCOME_EMP_SITUATION, cpi.*  FROM CHECKPOINT cp LEFT JOIN CHECKPOINTINFO cpi ON cp.CASEREVIEWID = cpi.CASEREVIEWID WHERE cpi.REASONCODE = 'OT3')ck3 ON pg.CASEID = ck3.CASEID                                        
LEFT JOIN ( SELECT cp.CASEID, cp.REASONCODE, cp.MONTH_OUTCOME_EMP_SITUATION, cpi.*  FROM CHECKPOINT cp LEFT JOIN CHECKPOINTINFO cpi ON cp.CASEREVIEWID = cpi.CASEREVIEWID WHERE cpi.REASONCODE = 'OT6')ck6 ON pg.CASEID = ck6.CASEID

如果有人能帮我把这张宽桌子变成一张长桌子，将不胜感激。

谢谢你

标签： sqloracle

解决方案

你需要unpivot为结果和原因列做。但首先，出于整体原因，您需要一个额外的列。这是查询：

with a as (
  select 12345 as case_reference,
    'Employed' as OUTCOME_EMP_SITUATION,
    'Employed' as MONTH1_EMP_SITUATION,
    'Outcome at 1 month' as MONTH1_REASON,
    'Employed' as MONTH3_EMP_SITUATION,
    'Outcome at 3 month' as MONTH3_REASON,
    'Employed' as MONTH6_EMP_SITUATION,
    'Outcome at 6 month' as MONTH6_REASON
  from dual
)
select
  case_reference,
  outcome_emp_situation,
  month_reason
from (
  select a.*,
    cast(null as varchar2(1000)) as reason
  from a
) a
unpivot(
  (Outcome_emp_situation, Month_Reason)
  for mon in (
    (OUTCOME_EMP_SITUATION, reason) as 0,
    (MONTH1_EMP_SITUATION, MONTH1_REASON) as 1,
    (MONTH3_EMP_SITUATION, MONTH3_REASON) as 3,
    (MONTH6_EMP_SITUATION, MONTH6_REASON) as 6
  )
)
order by mon asc

案例参考 | OUTCOME_EMP_SITUATION | MONTH_REASON      
-------------: | :-------------------- | :-----------------
         12345 | 受雇 | 无效的              
         12345 | 受雇 | 1个月的结果
         12345 | 受雇 | 3个月的结果
         12345 | 受雇 | 6个月的结果

db<>在这里摆弄

UPD：下面的解释。

关键字后面的元组unpivot是结果列名，for关键字后面的列标识产生该值的列组。里面的元组定义列的组：对于每个组，列的值将被传递给结果元组的相应（按位置）列，并且将使用关键字后定义in的列的值生成新行。foras

因此，如果您需要将更多列传输到每一行，则需要将新列添加到结果元组（在 之后unpivot）和内部的每个列组中in。如果由于某种原因您没有足够的列来传递某些组，您可以使用外部包装您的源查询select并为该组添加虚拟（或恒定值）列。

笔记：

  • 每个元组的数据类型应该相同（或可根据默认数据类型优先级进行转换）。即每个元组的同一位置的成员应该有相同的类型，不同位置的成员可能有不同的类型。
  • 您可以在多个组和职位中重复使用同一列。

推荐阅读