sql - 在单行中获取不同的列值

Question

这是数据:

id    name     period      data1    data2
===================================================
111   name1    monthly     aaaaa    bbbbb
111   name1    quaterly    ccccc    ddddd
111   name1    halfYearly  eeeee    fffff
111   name1    annually    ggggg    hhhhh

我想要查询在单行中获取数据的内容

id    name     monthlYdata1    monthlYdata2      quaterlydata1    quaterlydata2      halfYearlydata1    halfYearlydata2      annuallydata1    annuallydata2
==========================================================================================================================================================
111   name1    aaaaa           bbbbb             ccccc            ddddd              eeeee              fffff                ggggg            hhhhh

Answer 1

您没有指定您使用的是什么 RDBMS，但是，这将适用于所有这些:

select id,
  name,
  max(case when period = 'monthly' then data1 end) as MonthlyData1,
  max(case when period = 'monthly' then data2 end) as MonthlyData2,
  max(case when period = 'quaterly' then data1 end) as quarterlyData1,
  max(case when period = 'quaterly' then data2 end) as quarterlyData2,
  max(case when period = 'halfYearly' then data1 end) as halfYearlyData1,
  max(case when period = 'halfYearly' then data2 end) as halfYearlyData2,
  max(case when period = 'annually' then data1 end) as annuallyData1,
  max(case when period = 'annually' then data2 end) as annuallyData2
from yourtable
group by id, name

见 SQL Fiddle with Demo

如果您使用的 RDBMS 具有 PIVOT函数，那么您可以执行以下操作，同时使用 UNPIVOT和 PIVOT以产生结果。正如 Andriy M 指出的那样 UNPIVOT假设 data1 的数据类型和 data2是相同的类型，如果不是，则需要转换为 UNPIVOT数据:

Oracle 11g:

select *
from
(
  select id, name, value, 
    period||data new_col
  from yourtable
  unpivot
  (
    value for data in (data1, data2)
  ) u
) x
pivot
(
  max(value)
  for new_col in ('monthlyDATA1', 'monthlyDATA2',
                  'quaterlyDATA1', 'quaterlyDATA2',
                  'halfYearlyDATA1', 'halfYearlyDATA2',
                  'annuallyDATA1', 'annuallyDATA2')
) p

见 SQL Fiddle with Demo

SQL 服务器:

select *
from
(
  select id, name, value, 
    period+data new_col
  from yourtable
  unpivot
  (
    value for data in (data1, data2)
  ) u
) x
pivot
(
  max(value)
  for new_col in ('monthlyDATA1', 'monthlyDATA2',
                  'quaterlyDATA1', 'quaterlyDATA2',
                  'halfYearlyDATA1', 'halfYearlyDATA2',
                  'annuallyDATA1', 'annuallyDATA2')
) p