0

I'm trying to create a simple pivot-like report, which I managed to do with SQL Server's pivot, but now the SQL needs to run on SQL Server and Postgresql.

So, I've changed the SQL to use CASE like this:

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

The "..." is where a complex subquery goes, the output of which is:

SPACENAME               PERMTYPE
Testware Releases       EDITSPACE
Testware Releases       VIEWSPACE
Documentation           VIEWSPACE

I'm trying to get a report like:

SPACENAME          Read Access  Write Access
Testware Releases       X           X
Documentation           X

But instead I'm getting:

SPACENAME          Read Access  Write Access
Testware Releases                   X
Testware Releases       X   
Documentation           X

I built my CASE upon another example here: PostgreSQL crosstab() alternative with CASE and aggregates)

There are only 2 differences:

  1. The other answer only puts one column in the GROUP BY. I agree that this is what makes sense, but when I do that I get an error: Column 'sub.PERMTYPE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  2. The other answer uses a sum() inside the CASE. If I do this (along with the single column in GROUP BY), I can get it work, but it shows a numeric-based report, but I really just want the "X" in the right column...

Is there any way to get the X's in the columns instead of a number?

jimtut
  • 2,366
  • 2
  • 16
  • 37

2 Answers2

3

MAX the CASE's, and just group on 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
LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • Works perfectly, thanks! Any explanation as to why `MAX` does the right thing here? I'm now thinking that everything in a pivot-like report like this that is NOT in the `GROUP BY` has to be a math-like function, and `MAX` is the only math-like function that can also handle strings? – jimtut Mar 08 '19 at 23:11
  • 1
    It's nothing magic or some complicated math actually. A MAX takes the maximum value. and 'X' is bigger than an empty string or null. If you run it without the GROUP BY and the MAX, then look at those results and imagine it compressing them to their alphabetically highest value. If you return numbers in the CASE it would be the highest numeric values – LukStorms Mar 08 '19 at 23:22
  • The aggregate functions AVG, SUM expect numbers as one would expect. But functions as MAX, MIN, COUNT, STRING_AGG are fine with most types. – LukStorms Mar 08 '19 at 23:31
  • Thanks. So, is my assumption correct, that every item returned in the top-level `SELECT` that is *not* in the `GROUP BY` must be one of these math/aggregate functions? That's why I either had to include my "Access" columns in the `GROUP BY` (which gave too many rows), or use a math/aggregate function on them? – jimtut Mar 09 '19 at 04:45
  • The answer to that is: [it depends](https://stackoverflow.com/a/5986159/4003419). For most RDBMS versions that assumption is correct. – LukStorms Mar 09 '19 at 06:23
  • @jimtut An example when it works in PostGreSql on *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=116b7a5f6aab527d9af7beba5defe0ab)*. But change the RDBMS to MS Sql Server and you get an error. Postgres allows it because it's grouped on the PK of table 1. – LukStorms Mar 09 '19 at 08:47
0

You want the answer to be grouped by Spacename and your 2 aliased columns?

In SQL Server you can try something similar: 

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
L0uis
  • 703
  • 5
  • 8
  • Interesting idea, but by putting the individual "Access" columns in the top-level `GROUP BY`, it's still making the data come out in separate rows. If you leave them out, it's back to the old error: "Column 'sub2.Read Access' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause – jimtut Mar 08 '19 at 22:56
  • I think you want to group by the alias, I can't test it exactly as your case, but maybe you can find a better set of examples here: https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by – L0uis Mar 08 '19 at 23:00