• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

Case and Group By

I am writing a query which acts a bit like a piviot table. To best explain my problem here is my current code

CASE WHEN Activity1 = 1 THEN Tb1.nameELSE NULL END AS Diag1,
                      CASE WHEN Activity1 = 2 THEN Tb1.nameELSE NULL END AS Dia2,
                      CASE WHEN Activity1 = 3 THEN Tb1.nameELSE NULL END AS Dia3,
                      CASE WHEN Activity1 = 4 THEN Tb1.nameELSE NULL END AS Dia4,

The results which I get back for this is:

ID      Dia1      Dia2      Dia3      Dia4
1      Jo      NULL      NULL      NULL                  
1      NULL      Jo      NULL      NULL                                                
What I was after was

ID      Dia1      Dia2      Dia3      Dia4
1      Jo      Jo      NULL      NULL                  

I have tried group by but it does not seem to work.
                                                                  
0
Mr_Shaw
Asked:
Mr_Shaw
  • 4
  • 3
1 Solution
 
reb73Commented:
Try something like this -

SELECT ID,
MAX(CASE WHEN Activity1 = 1 THEN Tb1.nameELSE NULL END) AS Dia1,
MAX(CASE WHEN Activity1 = 2 THEN Tb1.nameELSE NULL END) AS Dia2,
MAX(CASE WHEN Activity1 = 3 THEN Tb1.nameELSE NULL END) AS Dia3,
MAX(CASE WHEN Activity1 = 4 THEN Tb1.nameELSE NULL END) AS Dia4,
FROM table
GROUP BY ID
0
 
Mr_ShawAuthor Commented:
what does the MAX do
0
 
reb73Commented:
It'll get the maximum value (numeric or alphanumeric) for a group by element
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Mr_ShawAuthor Commented:
it did not seem to work.
0
 
reb73Commented:
Remove the comma after Dia4 and add a space between name and ELSE and retry -

SELECT ID,
MAX(CASE WHEN Activity1 = 1 THEN Tb1.name ELSE NULL END) AS Dia1,
MAX(CASE WHEN Activity1 = 2 THEN Tb1.name ELSE NULL END) AS Dia2,
MAX(CASE WHEN Activity1 = 3 THEN Tb1.name ELSE NULL END) AS Dia3,
MAX(CASE WHEN Activity1 = 4 THEN Tb1.name ELSE NULL END) AS Dia4
FROM table
GROUP BY ID
0
 
Mr_ShawAuthor Commented:
What i meant was that the statement executes, but does not bring back the results which I am looking for.
0
 
Mr_ShawAuthor Commented:
thanks.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now