CODEWITHSUNDEEP
sqlsql-servert-sql
Raju Kumar
Raju Kumar

Reputation: 1265

populate multiple variables based on row

I have the following variables 

  Declare @test1 int,
            @test2 int,
            @test3 int

is there a way to populate the above variables based on the rows in a table? currently im doing this.

select @test1 = value from tables where somecode = 'test1'
select @test2 = value from tables where somecode = 'test2'
select @test3 = value from tables where somecode = 'test3'

Is there a better way to do this? i.e in one single select statement?

something like this is it possible?

SELECT @test1 = CASE code WHEN 'test1' THEN value END,
       @test2 = CASE code WHEN 'test2' THEN value END
  FROM myTable

Upvotes: 0

Views: 113

Answers (1)

Andrew
Andrew

Reputation: 8703

    select
    @test1 = max(case when <testcolumn> = 'test1' then col1 else null end),
    @test2 = max(case when <testcolumn> = 'test2 ' then col2 else null end),
    ...
    from
    <your table>
    where
   test column in ('test1','test2')

Depending on your data, you may need to select distinct or some sort of aggregate to get it exactly right.

SQL Fiddle

Upvotes: 1

Related Questions