SQL Server - Complex Dynamic Pivot columns

问题:

I have two tables "Controls" and "ControlChilds"

Parent Table Structure:

Create table Controls(
    ProjectID Varchar(20) NOT NULL,
    ControlID INT NOT NULL,
    ControlCode Varchar(2) NOT NULL,
    ControlPoint Decimal NULL,
    ControlScore Decimal NULL,
    ControlValue Varchar(50)
)

Sample Data

ProjectID | ControlID | ControlCode | ControlPoint | ControlScore | ControlValue
P001        1           A            30.44            65           Invalid
P001        2           C            45.30            85           Valid

Child Table Structure:

Create table ControlChilds(
    ControlID INT NOT NULL,
    ControlChildID INT NOT NULL,
    ControlChildValue Varchar(200) NULL 
)

Sample Data

ControlID | ControlChildID | ControlChildValue
1           100              Yes
1           101              No
1           102              NA  
1           103              Others 
2           104              Yes
2           105              SomeValue

Output should be in a single row for a given ProjectID with all its Control values first & followed by child control values (based on the ControlCode (i.e.) ControlCode_Child (1, 2, 3...) and it should look like this

Also, I tried this PIVOT query and I am able to get the ChildControls table values but I dont know how to get the Controls table values.

DECLARE @cols AS NVARCHAR(MAX);

DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT 
                        distinct ',' + 
                        QUOTENAME(ControlCode + '_Child' + CAST(ROW_NUMBER() over(PARTITION BY ControlCode ORDER BY ControlChildID) AS Varchar(25)))
                      FROM Controls C
                      INNER JOIN ControlChilds CC 
                      ON C.ControlID = CC.ControlID 
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query ='SELECT *
FROM
(
  SELECT   
    (ControlCode + ''_Child'' + CAST(ROW_NUMBER() over(PARTITION BY ControlCode ORDER BY ControlChildID) AS Varchar(25))) As Code,
        ControlChildValue
  FROM Controls AS C
  INNER JOIN ControlChilds AS CC ON C.ControlID = CC.ControlID
) AS t
PIVOT 
(
  MAX(ControlChildValue) 
  FOR Code IN( ' + @cols + ' )' +
' ) AS p ; ';

 execute(@query);

Output I am getting:

Can anyone please help me on how to get the Controls table values in front of each ControlChilds table values?

回答1:

You've got a bit of a mess here because you have two tables in different structures and you want to pivot multiple columns. I would first start out writing a static version of your query to get the logic correct, then go through the process of writing a dynamic version.

Since you want to pivot multiple columns you'll need to unpivot the several columns in the Controls table first, then pivot. You've tagged this as SQL Server 2008, so you can use CROSS APPLY to unpivot the columns.

I'd suggest taking the following steps. First, unpivot the controls table:

select 
  ProjectId,
  col = ControlCode +'_'+col,
  val
from
(
  select 
    c.ProjectId,
    c.ControlCode,
    c.ControlPoint,
    c.ControlScore,
    c.ControlValue
  from controls c
) d
cross apply
(
  select 'ControlPoint', cast(controlpoint as varchar(10)) union all
  select 'ControlScore', cast(ControlScore as varchar(10)) union all
  select 'ControlValue', ControlValue
) c (col, val)

See SQL Fiddle with Demo. This is going to convert your multiple rows into multiple columns similar to:

| PROJECTID |            COL |     VAL |
|-----------|----------------|---------|
|      P001 | A_ControlPoint |   30.44 |
|      P001 | A_ControlScore |   65.00 |
|      P001 | A_ControlValue | Invalid |
|      P001 | C_ControlPoint |   45.30 |
|      P001 | C_ControlScore |   85.00 |
|      P001 | C_ControlValue |   Valid |

Second, get the data from the ControlChilds table into a similar format but use your row_number() to assign a sequence for each child:

select 
  projectId,
  col = ControlCode+'_'+'Child'+cast(seq as varchar(10)),
  ControlChildValue
from
(
  select c.ProjectId,
    c.ControlCode,
    cc.ControlChildValue,
    row_number() over(partition by c.ProjectId, c.ControlCode
                      order by cc.ControlChildId) seq
  from controls c
  inner join controlchilds cc
    on c.controlid = cc.controlid
) d

See SQL Fiddle with Demo. This gets the data from this table in the format:

| PROJECTID |      COL | CONTROLCHILDVALUE |
|-----------|----------|-------------------|
|      P001 | A_Child1 |               Yes |
|      P001 | A_Child2 |                No |
|      P001 | A_Child3 |                NA |
|      P001 | A_Child4 |            Others |
|      P001 | C_Child1 |               Yes |
|      P001 | C_Child2 |         SomeValue |

Now, you can easily use UNION ALL between the two queries and apply the PIVOT function:

select ProjectId,
  A_ControlPoint, A_ControlScore, A_ControlValue,
  A_Child1, A_Child2, A_Child3, A_Child4,
  C_ControlPoint, C_ControlScore, C_ControlValue,
  C_Child1, C_Child2
from
(
  select 
    ProjectId,
    col = ControlCode +'_'+col,
    val
  from
  (
    select 
      c.ProjectId,
      c.ControlCode,
      c.ControlPoint,
      c.ControlScore,
      c.ControlValue
    from controls c
  ) d
  cross apply
  (
    select 'ControlPoint', cast(controlpoint as varchar(10)) union all
    select 'ControlScore', cast(ControlScore as varchar(10)) union all
    select 'ControlValue', ControlValue
  ) c (col, val)
  union all
  select 
    projectId,
    col = ControlCode+'_'+'Child'+cast(seq as varchar(10)),
    ControlChildValue
  from
  (
    select c.ProjectId,
      c.ControlCode,
      cc.ControlChildValue,
      row_number() over(partition by c.ProjectId, c.ControlCode
                        order by cc.ControlChildId) seq
    from controls c
    inner join controlchilds cc
      on c.controlid = cc.controlid
  ) d
) src
pivot
(
  max(val)
  for col in (A_ControlPoint, A_ControlScore, A_ControlValue,
              A_Child1, A_Child2, A_Child3, A_Child4,
              C_ControlPoint, C_ControlScore, C_ControlValue,
              C_Child1, C_Child2)
) piv;

See SQL Fiddle with Demo.

Now that you have the correct logic, you can convert this to a dynamic SQL version:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col) 
                    from 
                    (
                      select ControlCode,
                        col = ControlCode +'_'+col,
                        seq, 
                        so
                      from controls
                      cross apply
                      (
                        select 'ControlPoint', 0, 0 union all
                        select 'ControlScore', 0, 1 union all
                        select 'ControlValue', 0, 2 
                      ) c (col, seq, so)
                      union all
                      select  ControlCode,
                        col = ControlCode+'_'+'Child'+cast(seq as varchar(10)),
                        seq, 
                        3
                      from
                      (
                        select ControlCode, 
                          row_number() over(partition by c.ProjectId, c.ControlCode
                                                  order by cc.ControlChildId) seq
                        from controls c
                        inner join controlchilds cc
                          on c.controlid = cc.controlid
                      ) d
                    ) src
                    group by ControlCode, seq, col, so
                    order by ControlCode, so, seq
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ProjectId, ' + @cols + ' 
            from 
            (
              select ProjectId,
                col = ControlCode +''_''+col,
                val
              from
              (
                select 
                  c.ProjectId,
                  c.ControlCode,
                  c.ControlPoint,
                  c.ControlScore,
                  c.ControlValue
                from controls c
              ) d
              cross apply
              (
                select ''ControlPoint'', cast(controlpoint as varchar(10)) union all
                select ''ControlScore'', cast(ControlScore as varchar(10)) union all
                select ''ControlValue'', ControlValue
              ) c (col, val)
              union all
              select 
                projectId,
                col = ControlCode+''_Child''+cast(seq as varchar(10)),
                ControlChildValue
              from
              (
                select c.ProjectId,
                  c.ControlCode,
                  cc.ControlChildValue,
                  row_number() over(partition by c.ProjectId, c.ControlCode
                                    order by cc.ControlChildId) seq
                from controls c
                inner join controlchilds cc
                  on c.controlid = cc.controlid
              ) d
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + ')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. I wrote the dynamic version to keep the columns in the order that you used in your sample. This can be done by using a sort order type of value.

This gives a final result of:

| PROJECTID | A_CONTROLPOINT | A_CONTROLSCORE | A_CONTROLVALUE | A_CHILD1 | A_CHILD2 | A_CHILD3 | A_CHILD4 | C_CONTROLPOINT | C_CONTROLSCORE | C_CONTROLVALUE | C_CHILD1 |  C_CHILD2 |
|-----------|----------------|----------------|----------------|----------|----------|----------|----------|----------------|----------------|----------------|----------|-----------|
|      P001 |          30.44 |          65.00 |        Invalid |      Yes |       No |       NA |   Others |          45.30 |          85.00 |          Valid |      Yes | SomeValue |

回答2:

To PIVOT your data you first need to UNPIVOT part of it, the part in the Controls table.

The preparation query become

SELECT ProjectID, ControlCode + '_' + [Field] [Field], Value
FROM   (SELECT ProjectID
             , ControlCode
             , CAST(ControlPoint AS SQL_Variant) ControlPoint
             , CAST(ControlScore AS SQL_Variant) ControlScore
             , CAST(ControlValue AS SQL_Variant) ControlValue
        FROM Controls C) D
       UNPIVOT
       (Value FOR [Field] IN (ControlPoint, ControlScore, ControlValue)) p
UNION ALL
SELECT ProjectID, ControlCode + '_Child' + RowID [Field], Value
FROM   (SELECT C.ProjectID
             , C.ControlCode
             , RowID = CAST(ROW_NUMBER() 
                 OVER (PARTITION BY CC.ControlID 
                       ORDER BY CC.ControlChildID) AS VARCHAR)
             , CAST(CC.ControlChildValue AS SQL_Variant) ControlChildValue
        FROM Controls C
             INNER JOIN ControlChilds CC ON C.ControlID = CC.ControlID) D
       UNPIVOT
       (Value FOR [Field] IN (ControlChildValue)) p

SQLFiddle demo

to have the data in the format

PROJECTID | FIELD          | VALUE
----------+----------------+----------
P001      | A_ControlPoint | 30
P001      | A_ControlScore | 65
P001      | A_ControlValue | Invalid
P001      | C_ControlPoint | 45
P001      | C_ControlScore | 85
P001      | C_ControlValue | Valid
P001      | A_Child1       | Yes
P001      | A_Child2       | No
P001      | A_Child3       | NA
P001      | A_Child4       | Others
P001      | C_Child1       | Yes
P001      | C_Child2       | SomeValue

now it's possible to procede to PIVOT

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

WITH Q AS (
  SELECT ProjectID, ControlCode + '_' + [Field] [Field], Value
       , ControlCode
       , ID = CASE WHEN [Field] = 'ControlPoint' THEN 1
                   WHEN [Field] = 'ControlScore' THEN 2
                   WHEN [Field] = 'ControlValue' THEN 3
              END
  FROM   (SELECT ProjectID
               , ControlCode
               , CAST(ControlPoint AS SQL_Variant) ControlPoint
               , CAST(ControlScore AS SQL_Variant) ControlScore
               , CAST(ControlValue AS SQL_Variant) ControlValue
          FROM Controls C) D
         UNPIVOT
         (Value FOR [Field] IN (ControlPoint, ControlScore, ControlValue)) p
  UNION ALL
  SELECT ProjectID, ControlCode + '_Child' + RowID [Field], Value
       , ControlCode
       , ID = RowID + 3
  FROM   (SELECT C.ProjectID
               , C.ControlCode
               , RowID = CAST(ROW_NUMBER() OVER (PARTITION BY CC.ControlID ORDER BY CC.ControlChildID) AS VARCHAR)
               , CAST(CC.ControlChildValue AS SQL_Variant) ControlChildValue
          FROM Controls C
               INNER JOIN ControlChilds CC ON C.ControlID = CC.ControlID) D
         UNPIVOT
         (Value FOR [Field] IN (ControlChildValue)) p
)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME([Field])
                      FROM Q
                      ORDER BY ControlCode, ID
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '')

SELECT @query ='
WITH Q AS (
  SELECT ProjectID, ControlCode + ''_'' + [Field] [Field], Value
  FROM   (SELECT ProjectID
               , ControlCode
               , CAST(ControlPoint AS SQL_Variant) ControlPoint
               , CAST(ControlScore AS SQL_Variant) ControlScore
               , CAST(ControlValue AS SQL_Variant) ControlValue
          FROM Controls C) D
         UNPIVOT
         (Value FOR [Field] IN (ControlPoint, ControlScore, ControlValue)) p
  UNION ALL
  SELECT ProjectID, ControlCode + ''_Child'' + RowID [Field], Value
  FROM   (SELECT C.ProjectID
               , C.ControlCode
               , RowID = CAST(ROW_NUMBER() OVER (PARTITION BY CC.ControlID ORDER BY CC.ControlChildID) AS VARCHAR)
               , CAST(CC.ControlChildValue AS SQL_Variant) ControlChildValue
          FROM Controls C
               INNER JOIN ControlChilds CC ON C.ControlID = CC.ControlID) D
         UNPIVOT
         (Value FOR [Field] IN (ControlChildValue)) p
)
SELECT *
FROM   (SELECT ProjectID, [Field], Value FROM Q) AS t
       PIVOT 
       (MAX(Value) FOR [Field] IN( ' + @cols + ' )) AS p ;'

execute(@query);

SQLFiddle demo

To get the field in the wanted order that specific order must be forced in the columns string, so in the SELECT ... FOR XML, as there is no usable order column a new one need to be created, that the meaning of the ID in the CTE