I have a list returning from select statement.
For example
select type, id
from userlist
returns:
type id
1 102
2 125
3 156
Now I want to assign variable values based on the type. Is it possible in single statement?
Like
SELECT
case when type = 1 then @frontenduser = id
when type = 2 then @backenduser = id
when type = 3 then @newuser = id
end
FROM
(SELECT type, id
FROM userlist) AS tbl1
Alternative to pivot:
select
@frontenduser = case type when 1 then id else @frontenduser end,
@backenduser = case type when 2 then id else @backenduser end,
@newuser = case type when 3 then id else @newuser end
from userlist
or the same, using iif operator (if you use SqlServer 2012 or later):
select
@frontenduser = iif(type = 1, id, @frontenduser),
@backenduser = iif(type = 2, id, @backenduser),
@newuser = iif(type = 3, id, @newuser)
from userlist
You can if you pivot the table.
select
@frontenduser = [1],
@backenduser = [2],
@newuser = [3]
from
(
select [type], id
from userlist
) As tbl
PIVOT
(
MAX(id)
FOR [type] IN([1],[2],[3])
) As pivotTable
No, you can't do that. What you can do is restrict the query by type. For example, WHERE type = 1 and have one query for each type in your table. Or you could send the results to a varchar and parse it later.
With that being said, the best course of action would be to normalize your table. Then you wouldn't have to deal with this situation.
Selecting into a variable is only good for a single result. The data is already identifying which users are in different types i.e. frontenduser/backenduser/newuser so if you want to use this information to do something, you will need to do something else!
For instance, if you want to 'do something' for each type of user you could use a cursor (I personally don't like them but you can...).
In Pseudo code:
Fill a cursor with users
For each user in the cursor
Begin
If the user is of type 1 Then do something
If the user is of type 2 Then do something else
End
etc...
