sql - 根据表列动态合并两个或多个列？

Question

我有一个名为 SectionNames 的表，如下所示

SectionID   SectionCode     Subsection   1            xYz         Individual   2            xYz         Family   3            CYD         Friends   4            PCPO        level1   5            PCPO        level2   6            PCPO        level3

So on. So in future we can add one or more subsections for each section code.

And have one more table which is a reference table for above SectionNames table and Employee table with employee data.

ID    EmployeeID    SectionID   Cost    1           1              1    $2002           1              2    $300    3           1              3    $40 4           1              4    $10 5           1              5    No Level6           1              6    No Level7           1              7    $20 8           1              8    No Level9           1              9    No Level

So Iwant the out put from these tables should look like:

EmployeeID     Individual_xyz_Cost    Family_xyz_Cost    Friends_xyz_cost  level1_PCPO_cost  level2_PCPO_Cost
   1                 $200                 $300               $400              $10             NoLevel

我的员工表中存在的员工记录很少。我希望这是动态的。就像如果将来如果为 XYZ 部分添加一个名为 Relatives 的子部分，那么我的查询应该返回 Relatives_XYZ_Cost。

如何动态编写此查询？

Answer 1

您需要使用 PIVOT 函数将数据从列转换为行。如果您要有未知数量的值需要作为列，那么您将需要使用动态 SQL。

先看静态或硬编码版本再转换成动态SQL版本比较容易。当您有已知数量的值时，使用静态版本:

select *
from
(
  select e.employeeid,
    s.subsection +'_'+s.sectioncode+'_Cost' Section,
    e.cost
  from employee e
  inner join sectionnames s
    on e.sectionid = s.sectionid
) src
pivot
(
  max(cost)
  for section in (Individual_xYz_Cost, Family_xYz_Cost,
                  Friends_CYD_Cost, level1_PCPO_Cost,
                  level2_PCPO_Cost, level3_PCPO_Cost)
) piv;

参见 SQL Fiddle with Demo .

如果您需要灵活的查询，那么您会将其转换为使用动态 SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(subsection +'_'+sectioncode+'_Cost') 
                    from SectionNames
                    group by subsection, sectioncode, sectionid
                    order by sectionid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT employeeid,' + @cols + ' 
              from 
             (
                select e.employeeid,
                  s.subsection +''_''+s.sectioncode+''_Cost'' Section,
                  e.cost
                from employee e
                inner join sectionnames s
                  on e.sectionid = s.sectionid
            ) x
            pivot 
            (
                max(cost)
                for section in (' + @cols + ')
            ) p '

execute(@query)

参见 SQL Fiddle with Demo

两者的结果是:

| EMPLOYEEID | INDIVIDUAL_XYZ_COST | FAMILY_XYZ_COST | FRIENDS_CYD_COST | LEVEL1_PCPO_COST | LEVEL2_PCPO_COST | LEVEL3_PCPO_COST |
----------------------------------------------------------------------------------------------------------------------------------
|          1 |                $200 |            $300 |              $40 |              $10 |         No Level |         No Level |