Ggtcf

Solving a recurrence relation (poker chips)

What is the most common color to indicate the input-field is disabled?

What do you call someone who asks many questions?

Which is the best way to check return result?

Arrow those variables!

Assassin's bullet with mercury

Ambiguity in the definition of entropy

Detention in 1997

Can a virus destroy the BIOS of a modern computer?

Why doesn't using multiple commands with a || or && conditional work?

What is a romance in Latin?

Is there an expression that means doing something right before you will need it rather than doing it in case you might need it?

Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?

What are some good books on Machine Learning and AI like Krugman, Wells and Graddy's "Essentials of Economics"

What reasons are there for a Capitalist to oppose a 100% inheritance tax?

Is it inappropriate for a student to attend their mentor's dissertation defense?

Are there any examples of a variable being normally distributed that is *not* due to the Central Limit Theorem?

How seriously should I take size and weight limits of hand luggage?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

Unlock My Phone! February 2018

Determining Impedance With An Antenna Analyzer

CAST throwing error when run in stored procedure but not when run as raw query

Short story with a alien planet, government officials must wear exploding medallions

Watching something be piped to a file live with tail

Using CASE for a pivot-like function in SQL Server and Postgresql

SQL - using alias in Group ByDo all columns in a SELECT list have to appear in a GROUP BY clausePostgreSQL crosstab() alternative with CASE and aggregatesAdd a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?LEFT JOIN vs. LEFT OUTER JOIN in SQL ServerFunction vs. Stored Procedure in SQL ServerHow do I UPDATE from a SELECT in SQL Server?Find all tables containing column with specified name - MS SQL ServerHow to Delete using INNER JOIN with SQL Server?psql: FATAL: database “<user>” does not exist

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?

sql sql-server postgresql

share|improve this question

asked Mar 8 at 22:30

jimtutjimtut

1,84121329

add a comment | 

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?

sql sql-server postgresql

share|improve this question

asked Mar 8 at 22:30

jimtutjimtut

1,84121329

add a comment | 

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?

sql sql-server postgresql

share|improve this question

asked Mar 8 at 22:30

jimtutjimtut

1,84121329

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

sql sql-server postgresql

sql sql-server postgresql

share|improve this question

asked Mar 8 at 22:30

jimtutjimtut

1,84121329

share|improve this question

asked Mar 8 at 22:30

jimtutjimtut

1,84121329

asked Mar 8 at 22:30

jimtutjimtut

1,84121329

asked Mar 8 at 22:30

jimtutjimtut

1,84121329

2 Answers
2

active

oldest

votes

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

share|improve this answer

answered Mar 8 at 22:37

LukStormsLukStorms

14.1k31734

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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 8 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 8 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 9 at 4:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The answer to that is: it depends. For most RDBMS versions that assumption is correct.
  
  – LukStorms
  Mar 9 at 6:23
  
  

  
  
  
  

 | 
show 1 more comment

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

share|improve this answer

answered Mar 8 at 22:46

L0uisL0uis

55935

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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: stackoverflow.com/questions/3841295/sql-using-alias-in-group-by
  
  – L0uis
  Mar 8 at 23:00
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55071913%2fusing-case-for-a-pivot-like-function-in-sql-server-and-postgresql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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

share|improve this answer

answered Mar 8 at 22:37

LukStormsLukStorms

14.1k31734

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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 8 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 8 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 9 at 4:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The answer to that is: it depends. For most RDBMS versions that assumption is correct.
  
  – LukStorms
  Mar 9 at 6:23
  
  

  
  
  
  

 | 
show 1 more comment

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

share|improve this answer

answered Mar 8 at 22:37

LukStormsLukStorms

14.1k31734

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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 8 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 8 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 9 at 4:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The answer to that is: it depends. For most RDBMS versions that assumption is correct.
  
  – LukStorms
  Mar 9 at 6:23
  
  

  
  
  
  

 | 
show 1 more comment

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

share|improve this answer

answered Mar 8 at 22:37

LukStormsLukStorms

14.1k31734

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

share|improve this answer

answered Mar 8 at 22:37

LukStormsLukStorms

14.1k31734

answered Mar 8 at 22:37

LukStormsLukStorms

14.1k31734

answered Mar 8 at 22:37

LukStormsLukStorms

14.1k31734

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

share|improve this answer

answered Mar 8 at 22:46

L0uisL0uis

55935

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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: stackoverflow.com/questions/3841295/sql-using-alias-in-group-by
  
  – L0uis
  Mar 8 at 23:00
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 8 at 22:46

L0uisL0uis

55935

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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: stackoverflow.com/questions/3841295/sql-using-alias-in-group-by
  
  – L0uis
  Mar 8 at 23:00
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 8 at 22:46

L0uisL0uis

55935

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

share|improve this answer

answered Mar 8 at 22:46

L0uisL0uis

55935

answered Mar 8 at 22:46

L0uisL0uis

55935

answered Mar 8 at 22:46

L0uisL0uis

55935