我正在尝试创建一个简单的类似枢轴的报告,我设法使用SQL Server的pivot
来进行,但是现在SQL需要在SQL Server和Postgresql上运行。
所以,我将SQL更改为使用CASE
:
SELECT SPACENAME
, CASE WHEN PERMTYPE='VIEWSPACE' THEN 'X' END AS "Read Access"
, CASE WHEN PERMTYPE='EDITSPACE' THEN 'X' END AS "Write Access"
FROM ( ...) sub
GROUP BY SPACENAME, PERMTYPE
ORDER BY SPACENAME
" ..."是一个复杂的子查询所在的地方,其输出是:
SPACENAME PERMTYPE
Testware Releases EDITSPACE
Testware Releases VIEWSPACE
Documentation VIEWSPACE
我正在尝试获得类似的报告:
SPACENAME Read Access Write Access
Testware Releases X X
Documentation X
但是我得到了:
SPACENAME Read Access Write Access
Testware Releases X
Testware Releases X
Documentation X
我在这里的另一个示例上构建了我的 CASE
只有2个差异:
- 另一个答案仅将一列放在
GROUP BY
中。我同意这是有道理的,但是当我这样做时,我会收到一个错误:列" sub.permtype'在选择列表中无效,因为它在汇总函数或croups by子句中都不包含。 - 另一个答案在
CASE
内使用sum()
。如果我这样做(以及GROUP BY
中的单列),我可以使它起作用,但是它显示了一个基于数字的报告,但我真的只想在右列中使用" x" ...
有什么办法可以将X在列中获取而不是数字?
最大案例,然后在spacename上进行分组
SELECT SPACENAME
, MAX(CASE WHEN PERMTYPE='VIEWSPACE' THEN 'X' ELSE '' END) AS "Read Access"
, MAX(CASE WHEN PERMTYPE='EDITSPACE' THEN 'X' ELSE '' END) AS "Write Access"
FROM ( <<your big sub-query>> ) sub
GROUP BY SPACENAME
ORDER BY SPACENAME
您希望答案由SpacName和您的2列分组?
在SQL Server中,您可以尝试类似的东西:
SELECT SPACENAME,Read Access,Write Access
FROM
(
SELECT SPACENAME
, CASE WHEN PERMTYPE='VIEWSPACE' THEN 'X' END AS "Read Access"
, CASE WHEN PERMTYPE='EDITSPACE' THEN 'X' END AS "Write Access"
FROM ( ...) --i do not have a way to duplicate your situation
) as sub2
GROUP BY SPACENAME,Read Access,Write Access