sql, case when then as

Question

After much searching the web and Stackoverflow, still looking for a way to use ALIAS to return columns without producing a new row/line.

The following works create the columns "Sig_1_Emp" and "Sig_3_Staff", but the data for Sig_1_Emp and Sig_3_Staff are not in the same row, but two rows.

CASE
    WHEN VisitSignatures.order = 1 THEN Employees.last_name

    END AS Sig_1_Emp,

CASE
    WHEN VisitSignatures.order = 3 THEN Employees.last_name

    END AS Sig_3_Staff

SO, would something like the follow work?

CASE WHEN VisitSignatures.order = 1 THEN Employees.last_name AS Sig_1_Emp
     WHEN VisitSignatures.order = 3 THEN Employees.last_name AS Sig_3_Staff
END

Here's the full query:

Select 
CV.clientvisit_id, 
CV.program_id, 
CV.visittype, 
CV.non_billable, 
CV.rev_timein, 

CASE
    WHEN CVSig.ord = 1 THEN Employees.last_name
    ELSE Null
    END AS Sig_1_Emp,

CASE
    WHEN CVSig.ord = 3 THEN Employees.last_name
    ELSE Null
    END AS Sig_3_Staff

From CV 
Inner Join CVSig On CV.clientvisit_id = CVSig.clientvisit_id 
Inner Join EmpSig On CVSig.employeesignature_id = EmpSig.employeesignature_id 
Inner Join Employees On EmpSig.emp_id = Employees.emp_id 

Where 
CV.program_id In (121, 123)     
And CV.rev_timein >= @param1 
And CV.rev_timein <= DATEADD(d, 1, @param2)

And a sample of the results:

+----------------+------------+-----------+------------+-----------+-------------+  
| clientvisit_id | program_id | visittype | rev_timein | sig_1_emp | sig_3_staff |
+----------------+------------+-----------+------------+-----------+-------------+  
| 1001           | 121        | M_Mgmnt   | 7/1/2014   |           | Nurse_Pat   |
| 1001           | 121        | M_Mgmnt   | 7/1/2014   | Doc_Sue   |             |
+----------------+------------+-----------+------------+-----------+-------------+

And this is what I'm hoping to acheive:

+----------------+------------+-----------+------------+-----------+-------------+  
| clientvisit_id | program_id | visittype | rev_timein | sig_1_emp | sig_3_staff |
+----------------+------------+-----------+------------+-----------+-------------+  
| 1001           | 121        | M_Mgmnt   | 7/1/2014   | Doc_Sue   | Nurse_Pat   |
+----------------+------------+-----------+------------+-----------+-------------+

My apologies for the confusion, and thank you for your patience. If this does not clarify my issue, please delete this post.

Answer 1

Given your update to the question, it's clear that what you're missing is a GROUP BY clause with appropriate aggregation functions.

Note that in your sample result, the first four columns (clientvisit_id, program_id, visittype, rev_timein) contain identical values in both sample rows. If you GROUP BY those four columns, the sample rows will be grouped into a single row because their values are the same. I'm not sure why you aren't showing non_billable here, but the concept extends to as many columns as you want to use to define distinct groups.

Since the sig_1_emp and sig_3_staff columns are not used for grouping, they must be used with one of the available aggregate functions as opposed to "naked" in the column list. The typical thing to do in this situation would be to use MIN() or MAX(), both of which ignore NULL rows and return only one of many values. They're equivalent when all the values in the group are equal, otherwise they give you whichever value is the minimum or maximum.

If you don't know confidently that all the non-NULL rows have the same value, then you'll have to figure out some other way of choosing or deriving a value, based on the rows that do not appear in the GROUP BY clause, or live with arbitrarily taking whichever value compares as the maximum or minimum. Keeping in mind that each data type may have its own comparison rules. Seems inadvisable.

So, try adding this to the end of your query:

GROUP BY clientvisit_id, program_id, visittype, rev_timein, non_billable

And wrapping your CASE expressions like so:

MAX(CASE WHEN CVSig.ord = 1 THEN Employees.last_name END) AS Sig_1_Emp,
MAX(CASE WHEN CVSig.ord = 3 THEN Employees.last_name END) AS Sig_3_Staff

And see if that does the trick (keeping in mind the caveats already stated). You could keep the ELSE NULL if you prefer, but the expression will be NULL if none of the cases are true regardless; I don't think being explicit is worth the real estate in this case (pun intended).

Answer 2

looking for a way to use ALIAS to return columns without producing a new row/line

The way you've phrased this question is a red flag that says you're probably not understanding a few important things about the form and function of your database.

A SELECT query, strictly speaking, returns a result set that has an inherent structure, independent of how it's displayed. So the query doesn't "produce" a new row or a new line, and if you think of your result in those terms you're going to run into this sort of trouble frequently. I won't go into detail here, but I highly recommend doing some reading about the relational model and set-based thinking.

You want to do two distinct things here:

1. Choose values for more than one attribute of your result set (Sig_1_Emp and Sig_1_Staff) based on only a single attribute of your table (VisitSignatures.order).
2. Aggregate values from multiple rows into a single row.

The CASE expression¹ isn't really designed to do either of these things on its own; it's simply an expression used when you need some conditional logic for a single column. You can't use a function or expression across multiple columns. However, you could use a separate expression for each clause:

SELECT
  CASE sig.order WHEN 1 THEN emp.last_name END AS Sig_1_Emp,
  CASE sig.order WHEN 3 THEN emp.last_name END AS Sig_3_Staff
FROM
  VisitSignatures AS sig
    JOIN
  Employees AS emp ON (...);

Note that I've aliased your tables to make the statement cleaner; you would fill in your join condition and any WHERE clauses needed. That would get you from the following (joined) table:

+-----------+---------------+
| sig.order | emp.last_name |
+-----------+---------------+
| 1         | 'SMITH'       |
| 1         | 'NGUYEN'      |
| 3         | 'FIELDS'      |
+-----------+---------------+

To this result set:

+-----------+-------------+
| Sig_1_Emp | Sig_3_Staff |
+-----------+-------------+
| 'SMITH'   | NULL        |
| 'NGUYEN'  | NULL        |
| NULL      | 'FIELDS'    |
+-----------+-------------+

So far you've only accomplished the first task. For the second, you must use aggregate functions. Which function you use depends on what you want to do, but if you want to count up the employees in each column, all you need to do is wrap a COUNT() function around your CASE expressions. Keep the aliases outside the function (and probably change them to something indicative of a count) and GROUP BY any non-aggregated columns requested in your query to get something like:

SELECT
  other_columns,
  COUNT(CASE sig.order WHEN 1 THEN emp.last_name END) AS Num_Sig_1,
  COUNT(CASE sig.order WHEN 3 THEN emp.last_name END) AS Num_Sig_3
FROM
  VisitSignatures AS sig
    JOIN
  Employees AS emp ON (...)
GROUP BY other_columns;

+-----+-----------+-----------+
| ... | Num_Sig_1 | Num_Sig_3 |
+-----+-----------+-----------+
| ... | 2         | 1         |
+-----+-----------+-----------+

---

Note that the CASE expressions gave you strings of characters in the intermediate result set – if all you want to do is count the non-NULL values, this may not be the most efficient way to go about it, depending on which DBMS you're using. For example, in MySQL it would be much more efficient to do this (ignoring the extra columns/table for brevity):

SELECT
  order = 1 AS Sig_1_Emp,
  order = 3 AS Sig_3_Staff
FROM
  VisitSignatures;

Producing:

+-----------+-------------+
| Sig_1_Emp | Sig_3_Staff |
+-----------+-------------+
| 1         | 0           |
| 1         | 0           |
| 0         | 1           |
+-----------+-------------+

Which you would aggregate using SUM() instead of COUNT() to get the same result as before. You haven't said which DBMS you're using, but even if it's not MySQL, you should carefully consider whether you actually need those last names in the intermediate result set. It's easy to design inefficient queries that run fast as long as there aren't too many rows in the table, but become problematic in the future.

On the other hand, it's possible that your DBMS might be able to optimize for emp.last_name in a way that references the value without storing it in an intermediate table, and is quite fast. That's something you would have to research on your own.

_{¹If you happen to be using MySQL, read: Case Expression vs Case Statement}

Answer 3

If I'm understanding your question correctly, you're trying to pivot your results. You can use the max aggregate with the case statement to do this:

select 
    otherfields,
    max(CASE WHEN VisitSignatures.order = 1 
             THEN Employees.last_name END) AS Sig_1_Emp,
    max(CASE WHEN VisitSignatures.order = 3 
             THEN Employees.last_name END) AS Sig_3_Staff
from tables (VisitSignatures and Employees and others)
group by otherfields

Whichever other fields you are selecting, you'll want to include in the group by clause.