我有两个表“Controls”和“ControlChilds”
父表结构：
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)
)
样本数据
ProjectID | ControlID | ControlCode | ControlPoint | ControlScore | ControlValue
P001 1 A 30.44 65 Invalid
P001 2 C 45.30 85 Valid
子表结构：
Create table ControlChilds(
ControlID INT NOT NULL,
ControlChildID INT NOT NULL,
ControlChildValue Varchar(200) NULL
)
样本数据
ControlID | ControlChildID | ControlChildValue
1 100 Yes
1 101 No
1 102 NA
1 103 Others
2 104 Yes
2 105 SomeValue
对于给定的 ProjectID，输出应位于单行中，首先是其所有控制值，然后是子控件值（基于 ControlCode（即）ControlCode_Child (1, 2, 3...)，并且应该如下所示
另外，我尝试了这个 PIVOT 查询，我能够获取 ChildControls 表值，但我不知道如何获取 Controls 表值。
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:
谁能帮助我了解如何获取每个 ControlChilds 表值前面的 Controls 表值？
这里有点混乱，因为您有两个不同结构的表，并且您想要旋转多个列。我首先会开始编写查询的静态版本以使逻辑正确，然后完成编写动态版本的过程。
由于您想要旋转多个列，因此您需要取消旋转中的多个列Controls先表，后枢轴。您已将其标记为 SQL Server 2008，因此您可以使用CROSS APPLY取消旋转列。
我建议采取以下步骤。首先，取消旋转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 摆弄演示 http://sqlfiddle.com/#!3/76778/12/0。这会将您的多行转换为多列，类似于：
| 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 |
其次，获取数据ControlChilds表成类似的格式，但使用你的row_number()为每个孩子分配一个序列：
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 摆弄演示 http://sqlfiddle.com/#!3/76778/13/0。这将从该表中获取以下格式的数据：
| 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 |
现在，您可以轻松使用UNION ALL在两个查询之间并应用 PIVOT 函数：
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 摆弄演示 http://sqlfiddle.com/#!3/76778/14/0.
现在您已经有了正确的逻辑，您可以将其转换为动态 SQL 版本：
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 摆弄演示 http://sqlfiddle.com/#!3/3db46/37/0。我编写了动态版本，以按照您在示例中使用的顺序保留列。这可以通过使用排序顺序类型的值来完成。
最终结果为：
| 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 |