Eliminating NULLs when using CASE in SQL Server SELECT statement

Question

I have a large, messy report to write that joins across 5 tables. There is one column in one table that is being used for several different values--essentially a "tag" column, where the tags are used in creative ways depending on what kind of miscellaneous metadata the users want to use.

As a result, my query for the report returns 3 nearly-identical rows that differ only in the "tag" column; for instance, I might get:

NAME TAG EMAIL  
BOB  A   [email protected]  
BOB  B   [email protected]  
BOB  C   [email protected]  

What I'd like to do is split the contents of the TAG column to be returned as 3 separate columns from the query, like this:

NAME A B C EMAIL
BOB  A B C [email protected]

So I tried using the SQL SERVER CASE/WHEN functionality to do this; I'm saying, for instance, when the value of the Tag column is "A", return it in column "A"; if it's "B," put it in "B"; etc. I thought this would return the above, but instead it gives me this:

NAME A    B    C    EMAIL
BOB  A    NULL NULL [email protected]
BOB  NULL B    NULL [email protected]
BOB  NULL NULL C    [email protected]

Which is clearly less than ideal.

Any thoughts, geniuses of Stack Overflow?

Answer 1

You need to pivot the data.

;with Report (name, tag, email) as
(
select 'BOB', 'A', '[email protected]' union
select 'BOB', 'B', '[email protected]' union
select 'BOB', 'C', '[email protected]' 
)
select * from Report
pivot 
( min(tag) for  tag in ([A], [B], [C]))  
as pvt

And the result of running the above query is this:

NAME    EMAIL         A  B  C
-----------------------------
BOB     [email protected]   A  B  C

Answer 2

try this:

   Select Name,
    Min (Case When tag = 'A' Then Tag End) A,
    Min (Case When tag = 'B' Then Tag End) B,
    Min (Case When tag = 'C' Then Tag End) C,
    email
   From tableName
   Group By Name, email

EDIT: to explain...
Anytime you use a Group By, you are telling the query processor that you want it to aggregate the results into "buckets", where each bucket is defined by unique values in the columns [or expressions] defined in the Group By clause. That means that the final result set will have one and only one row for each unique set of values in those columns [or expressions] defined in the Group By. All other columns or expressions used in the query, (other than those defined in the Group By), must be an expression based on an aggregation function (like Count(), Sum(), Avg(), Min(), Max(), etc...), that produce a value based on a calculation which will be applied to all the rows in the pre-aggregated result set. If, for example, I were to Group By the first character of the last name:

Select Left(LastName, 1), Count(*), 
   Sum(Salaray, Avg(Height), 
   Min(DateOfBirth), etc.
  From Table
  Group By Left(LastName, 1)

then I will get at most 26 rows in the output (one for each letter in the alphabet) and all other columns in my output must be based on some aggregation function to be applied to all the rows in the original set where the last name starts with an 'A", then all the rows where the last name starts with qa 'B' etc..

In your problem, the Group By is used simply to restrict the output set to one row per distinct user and email. Once this is done, there will be only one row per The other columns in the Select statement need to have a Min(), [Max() would work just as well), only in order to satisfy the syntacical requirement mentioned in bold italics above.. In your case, there will only be one non-null row in the set so taking the Min(), or the Max() is necessary only because of the syntax requirement...