CODEWITHSUNDEEP
t-sqlcase
Vishal
Vishal

Reputation: 12369

How to write the following SQL case statement?

I have resultset like -

Id     var_name    var_value
1      min_points     20
2      max_points     120
3      avg_points     50
4      total_points   320

Here is a query I have tried to write - 

select 
         @min_points =case
            when var_name='min_points' then var_value
        end,
        @max_points=case
            when var_name='max_points' then var_value
        end,
        @avg_points=case
            when var_name='avg_points' then var_value
        end,
        @total_points= case  
            when var_name='total_points' then var_value
        end
    from 
        **joined multiple tables**

But the above query does not work and I can understand why..but can anyone help me write a query that will basically help me store all the four var_values in the four variables I have by checking the var_names ?

Upvotes: 1

Views: 1934

Answers (4)

Martin Smith
Martin Smith

Reputation: 453028

You need to get the result set into a single row to avoid assigning to the same variables 4 different times. As it stands for each row in the result set the variables are being assigned which means that after the assignment 3 of them will not meet the condition and be NULL and 1 will be NOT NULL.

select 
         @min_points =max(case
            when var_name='min_points' then var_value
        end),
        @max_points=max(case
            when var_name='max_points' then var_value
        end),
        @avg_points=max(case
            when var_name='avg_points' then var_value
        end),
        @total_points= max(case  
            when var_name='total_points' then var_value
        end)
    from 
        **joined multiple tables**

Or alternatively you could keep the multiple assignments but just reassign the same value back to the variable if the row being processed is not the row of interest.

   @min_points = CASE
                   WHEN var_name = 'min_points' THEN var_value
                   ELSE @min_points
                 END

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460058

create table #activity(
ID  int,
var_name  varchar(20),
var_value  int,
)
INSERT INTO #activity VALUES(1,'min_points',20);
INSERT INTO #activity VALUES(2,'max_points',120);
INSERT INTO #activity VALUES(3,'avg_points',50);
INSERT INTO #activity VALUES(4,'total_points',320);

select MAX(CASE WHEN var_name='min_points' THEN  var_value end)as min_points, 
    MAX(CASE WHEN var_name='max_points' THEN  var_value end)as max_points,
    MAX(CASE WHEN var_name='avg_points' THEN  var_value end)as avg_points,
    MAX(CASE WHEN var_name='total_points' THEN  var_value end)as total_points
    from  #activity

Drop Table #activity;

Upvotes: 1

RepDetec
RepDetec

Reputation: 751

You have to use it like this:

  case (var_name)
    case 'min_points'
      var_value
    case 'max_points'
      var_value
    default
      var_value
  end

The thing is though, it looks like all of your cases give the same result, so I don't understand what you are trying to do.

Upvotes: 0

msarchet
msarchet

Reputation: 15242

You'd probably be better off doing this in multiple queries

select @min_points = var_value from activity where varmane = 'min_points'
select @max_points = var_value from activity where varmane = 'max_points'
select @avg_points = var_value from activity where varmane = 'avg_points'
select @total_points = var_value from activity where varmane = 'total_points'

Upvotes: 0

Related Questions