将行转换为具有固定行但在Server中内容未知的列

Question

我见过很多关于将行转成列的问题，但没有任何问题与我的问题相似，无法在这方面取得任何进展。

我有一个如下所示的数据集：

| candidate | qualification | unit | passed |
---------------------------------------------
| C1        | Q1            | U1-1 | 1      |
| C1        | Q1            | U1-2 | 1      |
| C1        | Q2            | U2-1 | 0      |
| C1        | Q2            | U2-2 | 1      |
| C2        | Q1            | U1-1 | 0      |
| C2        | Q1            | U1-2 | 0      |
| C2        | Q2            | U2-1 | 1      |
| C2        | Q2            | U2-2 | 1      |

其中，每个候选人可以注册多个资格，其中每个有多个单位，可以通过(1)或失败(0)。

我需要将数据转换成如下所示：

| candidate | qualification | unit_1 | unit_1_passed | unit_2 | unit_2_passed |
-------------------------------------------------------------------------------
| C1        | Q1            | U1-1   | 1             | U1-2   | 1             |
| C1        | Q2            | U2-1   | 0             | U2-2   | 1             |
| C2        | Q1            | U1-1   | 0             | U1-2   | 0             |
| C2        | Q2            | U2-1   | 1             | U2-2   | 1             |

这样，每个限定条件的单元和如果通过的话，都会被旋转成一列。

我知道总有一个固定数量的单位每个资格，但，我不知道什么单位名称将预先。

我的查询目前看起来如下：

select
  candidate,
  qualification,
  unit,
  passed
from exams

但我不知道如何将行转成列。

提前谢谢。

Answer 1

您可以使用窗口函数row_number()为每个候选/资格组中的每个单元分配一个级别，然后使用条件聚合进行枢轴操作：

select
    candidate,
    qualification,
    max(case when rn = 1 then unit end) unit_1,
    max(case when rn = 1 then passed end) unit_1_passed,
    max(case when rn = 2 then unit end) unit_2,
    max(case when rn = 2 then passed end) unit_2_passed
from (
    select
        t.*,
        row_number() over(
            partition by candidate, qualification 
            order by unit
        ) rn
    from exams t
) t
group by candidate, qualification 

Answer 2

这是我能想到的最好的

select 'C1' as 'candidate', 'Q1' as 'qualification', 'U1-1' as 'unit', '1' as 'passed'  into #tmp union
select 'C1', 'Q1', 'U1-2', '1' union
select 'C1', 'Q2', 'U2-1', '0' union
select 'C1', 'Q2', 'U2-2', '1' union
select 'C2', 'Q1', 'U1-1', '0' union
select 'C2', 'Q1', 'U1-2', '0' union
select 'C2', 'Q2', 'U2-1', '1' union
select 'C2', 'Q2', 'U2-2', '1' 


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

select * from #tmp

DECLARE @UnitNames VARCHAR(8000) 
SELECT @UnitNames = COALESCE(@UnitNames + ', ', '') + 'unit_' +unitnumber 
FROM (select distinct right(unit,1) unitnumber from #tmp)a


select candidate, unit, qualification, passed, 'unit_'+b.unitnumber as unitnumber into #tmp2 from #tmp a
inner join (select distinct right(unit,1) unitnumber from #tmp) b on right(a.unit,1) = b.unitnumber
where right(unit,1) = unitnumber
order by unitnumber

select * from #tmp2 order by unitnumber


SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.unitnumber) 
            FROM #tmp2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @colsUnit = STUFF((SELECT distinct ',' + QUOTENAME(c.unitnumber) 
            FROM #tmp2 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT candidate, qualification, unit, 
                ' + @colsUnit + ' from 
            (
                select candidate
                    , qualification
                    , passed
                    , unit
                    , unitnumber
                from #tmp2
           ) x
            pivot 
            (
                 max(passed)
                for unitnumber in (' + @colsUnit + ')
            ) p '


print @query

exec (@query)

drop table #tmp, #tmp2

这张桌子离你的桌子有点近：

​

希望它能帮上忙