Блог

Я уже 3 дня бьюсь головой об стену по этому поводу, поэтому надеюсь, что кто-нибудь сможет мне помочь.

У меня есть набор данных, к которым мне нужно выполнить некоторый поворот, но я не могу понять синтаксис.

Образец набора данных:

 Employee    LastName    FirstName   Status  Location    GroupBU     DeductionDesc   Active      Field                   Value
----------------------------------------------------------------------------------------------------------------------------------
12345       SMITH       JOHN            A       5555        9999    LIFE INS CHILD      Y       Deduction               8502
12345       SMITH       JOHN            A       5555        9999    LIFE INS CHILD      Y       EmployeeAmtPct          0.1
12345       SMITH       JOHN            A       5555        9999    LIFE INS CHILD      Y       EmployeeAnnSalary       10000
12345       SMITH       JOHN            A       5555        9999    LIFE INS CHILD      Y       EmployeeInsAmt          10000
67890       SMITH       JANE            A       5555        9999    LIFE INS SPOUSE     Y       Deduction               8501
67890       SMITH       JANE            A       5555        9999    LIFE INS SPOUSE     Y       EmployeeAmtPct          0.357
67890       SMITH       JANE            A       5555        9999    LIFE INS SPOUSE     Y       EmployeeAnnSalary       50000
67890       SMITH       JANE            A       5555        9999    LIFE INS SPOUSE     Y       EmployeeInsAmt          50000
 

Желаемый результат:

 Employee    LastName    FirstName   Status  Location    GroupBU [LIFE INS CHILD]    Deduction   EmployeeAmtPct  EmployeeAnnSalary   EmployeeInsAmt  [LIFE INS SPOUSE]   Deduction   EmployeeAmtPct  EmployeeAnnSalary   EmployeeInsAmt
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12345       SMITH       JOHN        A       5555        9999    LIFE INS CHILD      8502        0.1             10000               10000           NULL                NULL        NULL            NULL                NULL
67890       SMITH       JANE        A       5555        9999    NULL                NULL        NULL            NULL                NULL            LIFE INS SPOUSE     8501        0.357           50000               50000
 

Очевидно, что в моем «желаемом результате» у меня есть повторяющиеся имена столбцов, которые не будут работать, поэтому я предполагаю, что мне нужно будет придумать способ добавления / добавления чего-либо к каждому из них, чтобы сделать их уникальными, но я не смог понять, как это сделатьэто. Это также было бы необходимо, чтобы связать различные поля и значения с соответствующим «DeductionDesc» в окончательном выводе. Также было запрошено, чтобы значение в «DeductionDesc» было именем столбца в окончательном выводе, и если значение не существует, оно просто равно NULL (показано в выводе).

Мои исходные данные были созданы с использованием UNPIVOT, поскольку кажется, что это повысит вероятность успеха.

У меня около 65 уникальных значений в «DeductionDesc» и около 3500 уникальных записей о сотрудниках.

Вот текущая (и примерно 4-я итерация) кода, который у меня есть:

 select * 
from
    (
    select Employee,LastName,FirstName,Status,Location,GroupBU,DeductionDesc,Active,Field,Value 
    FROM MyTable
    ) unpvt
PIVOT 
    (
    MAX([DeductionDesc])
    FOR [DeductionDesc] IN ([LIFE INS SPOUSE],[LIFE INS CHILD UNUM POST TAX])
    ) pvt
PIVOT 
    (
    MAX([Value])
    FOR [Field] IN ([InsPlan],[EmployeeAnnSalary],[InsCoverage],[InsEmployeeRate],[EmployeeAmtPct],[InsLevelDesc],[InsCoverageDesc],[InsCarrierDesc],[InsPlanDesc],[InsLevel],[Deduction],[EmployeeInsAmt],[InsCarrier])
    ) pvt
 

Для удобства чтения и упрощения я удалил большинство элементов из первого сводного элемента в операторе «IN», но изначально там были перечислены все 65.

Буду признателен за любую помощь, которую кто-либо может оказать, чтобы добиться желаемого результата. Если требуется дополнительная информация, пожалуйста, дайте мне знать. Спасибо!

Предполагая здесь синтаксис SQL Server, но концепция применима к большинству вариантов SQL. Один из способов атаковать это — использовать конструкцию CASE:

 SELECT tA.Employee, tA.LastName, tA.FirstName, tA.Status, tA.Location,    
       tA.GroupBU, tB.Deduction, tB.EmployeeAmtPct

FROM your_table AS tA 

INNER JOIN

(SELECT Employee, SUM(CASE WHEN Field = 'Deduction'      THEN Value ELSE 0 END) AS Deduction,
                  SUM(CASE WHEN Field = 'EmployeeAmtPct' THEN Value ELSE 0 END) AS EmployeeAmtPct
                  ...and so on for all the fields that you want to transpose...
 FROM your_table 
 GROUP BY Employee) AS tB ON tA.Employee = tB.Employee
 

Я просто добавляю здесь вычеты и EmployeeAmtPct для краткости. По сути, это запрос с перекрестной таблицей. Он просто выполняет выборочное суммирование в зависимости от того, в какую категорию попадает «Поле». Предложение ELSE в операторе CASE гарантирует, что всегда есть значение для суммирования.

Если вы не возражаете перейти на динамический (измените #myTable на имя вашей таблицы)

 Declare @SQL varchar(max) = ''

Select @SQL = @SQL  SqlExpr
 From (
        Select Top 100 Percent *
              ,SqlExpr = case when ColNr=1 then char(13) ',' QuoteName(DeductionDesc) '=max(case when DeductionDesc=''' DeductionDesc ''' and Field=''' Field ''' then DeductionDesc else null end)' else '' end  char(13) ',' QuoteName(Field) '=max(case when DeductionDesc=''' DeductionDesc ''' and Field=''' Field ''' then Value else null end)'
         From (
                Select Distinct 
                       DeductionDesc
                      ,Field
                      ,ColNr  = Row_Number() over (Partition By DeductionDesc Order By Field)
                 From  #MyTable
              ) A Order By 1,3
      ) A 

Select @SQL = '
Select Employee
      ,LastName
      ,FirstName
      ,Status
      ,Location
      ,GroupBU' @SQL '
 From  #MyTable
 Group By 
       Employee
      ,LastName
      ,FirstName
      ,Status
      ,Location
      ,GroupBU 
'
--Print(@SQL)
Exec(@SQL)
 

ВОЗВРАТ