Declare @tbl table(SessionId varchar(max),ItemID_FK int,Roles varchar(max))
insert into @tbl
select distinct SessionID,ItemID_FK,Roles from tbl_Answers where ID_FK=@ID
SELECT ItemID_PK,ItemName,case when [Role1] IS NULL then 0 else [Role1] end as [Role1],
case when [Role2] IS NULL then 0 else [Role2] end as [Role2],
case when [Role3] IS NULL then 0 else [Role3] end as [Role3],
case when [Role4] IS NULL then 0 else [Role4] end as [Role4],
case when [Role5] IS NULL then 0 else [Role5] end as [Role5],
case when [Role6] IS NULL then 0 else [Role6] end as [Role6],
case when [Role7] IS NULL then 0 else [Role7] end as [Role7]
FROM
(
select items.ItemID_PK ,items.ItemName,count(ans.Roles) as cntRoles,ans.Roles from tbl_Items items Full join @tbl ans
on items.ItemID_PK=ans.ItemID_FK where items.ID_FK= @ID group by Roles,ItemName , items.ItemID_PK
) d PIVOT
(
max(cntRoles)
FOR Roles IN ([Role1],[Role2],[Role3],[Role4],[Role5],[Role6],[Role7])
) AS pvt order by ItemID_PK
我使用了上面的存储过程，得到的输出为
+----------+----------+-----+-----+-----+-----+-----+-----+-----+
|ItemID_PK |ItemName |Role1|Role2|Role3|Role4|Role5|Role6|Role7|
+----------+----------+-----+-----+-----+-----+-----+-----+-----+
| 111 | aaaaa | 6 | 5 | 0 | 5 | 1 | 4 | 2 |
| 222 | bbbbb | 1 | 1 | 7 | 2 | 0 | 3 | 1 |
+----------+----------+-----+-----+-----+-----+-----+-----+-----+
我有另一个查询，并得到以下输出。
Select Category,Answer,Roles
from tbl_Answers where ID_FK=1 and Category='OtherText'
+---------+--------+-----+
|Category |Answer |Roles|
+---------+--------+-----+
|OtherText| xxx |Role1|
|OtherText| yyy |Role1|
|OtherText| zzz |Role2|
|OtherText| xzx |Role3|
+---------+--------+-----+
我需要合并以上两个输出以生成结果
+----------+----------+-----+-----+-----+-----+-----+-----+-----+
|ItemID_PK |ItemName |Role1|Role2|Role3|Role4|Role5|Role6|Role7|
+----------+----------+-----+-----+-----+-----+-----+-----+-----+
| 111 | aaaaa | 6 | 5 | 0 | 5 | 1 | 4 | 2 |
| 222 | bbbbb | 1 | 1 | 7 | 2 | 0 | 3 | 1 |
| Null | Othertext| xxx | zzz | xzx | saa | | xxx | |
| Null | Othertext| yyy | | | zxz | | | |
+----------+----------+-----+-----+-----+-----+-----+-----+-----+
如何将第二个查询与第一个枢轴查询组合以获得上述结果？
提前致谢。
您可以只使用UNION ALL合并两个结果，但是需要将顶部查询中的角色从int转换为VARCHAR：
DECLARE @ID INT = 1;
WITH Ans AS
( SELECT DISTINCT SessionID,ItemID_FK,Roles
FROM tbl_Answers
WHERE ID_FK = @ID
), PivotData AS
( SELECT items.ItemID_PK,
items.ItemName,
cntRoles = COUNT(ans.Roles),
ans.Roles
FROM tbl_Items items
FULL JOIN Ans
ON items.ItemID_PK = ans.ItemID_FK
WHERE items.ID_FK = @ID
GROUP BY Roles,ItemName, items.ItemID_PK
)
SELECT ItemID_PK,
ItemName,
[Role1] = CAST(ISNULL([Role1], 0) AS VARCHAR(255)),
[Role2] = CAST(ISNULL([Role2], 0) AS VARCHAR(255)),
[Role3] = CAST(ISNULL([Role3], 0) AS VARCHAR(255)),
[Role4] = CAST(ISNULL([Role4], 0) AS VARCHAR(255)),
[Role5] = CAST(ISNULL([Role5], 0) AS VARCHAR(255)),
[Role6] = CAST(ISNULL([Role6], 0) AS VARCHAR(255)),
[Role7] = CAST(ISNULL([Role7], 0) AS VARCHAR(255))
FROM PivotData
PIVOT
( MAX(cntRoles)
FOR Roles IN ([Role1],[Role2],[Role3],[Role4],[Role5],[Role6],[Role7])
) AS pvt
UNION ALL
SELECT ItemID_PK = NULL,
ItemName = Category,
[Role1] = ISNULL([Role1], ''),
[Role2] = ISNULL([Role2], ''),
[Role3] = ISNULL([Role3], ''),
[Role4] = ISNULL([Role4], ''),
[Role5] = ISNULL([Role5], ''),
[Role6] = ISNULL([Role6], ''),
[Role7] = ISNULL([Role7], '')
FROM ( SELECT Category,Answer,Roles
FROM tbl_Answers
WHERE ID_FK = 1
AND Category = 'OtherText'
) pd
PIVOT
( MAX(Answer)
FOR Roles IN ([Role1],[Role2],[Role3],[Role4],[Role5],[Role6],[Role7])
) AS pvt
ORDER BY ItemID_PK;
注意，我更改了此表达式：
case when [Role2] IS NULL then 0 else [Role2] end
到
ISNULL([Role2], 0)
因为效果是一样的，但是要短得多。我还删除了表变量，并在公共表表达式中放置了相同的查询，因为填充表变量然后仅引用一次似乎是多余的。您将删除对实际表的索引和统计信息的使用，并不会因此获得任何好处。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句