SQL query store result of SELECT in local variable

Question

I create a query with some results reused  I search a way to put the result into a variable and use it   A simple way to see what I want something looking like this - I want this   DECLARE  result1        SET  result1   SELECT a b c FROM table1 SELECT a AS val FROM  result1 UNION SELECT b AS val FROM  result1 UNION SELECT c AS val FROM  result1   Not this     SELECT a AS val FROM  SELECT a b c FROM table1   UNION  SELECT b AS val FROM  SELECT a b c FROM table1   UNION  SELECT c AS val FROM  SELECT a b c FROM table1    It s not the result of this query that I m concerned with  but instead    to stop selecting the result so many times - in my sample  I reselected the table 3 times the query of  result1 is usually so much more complex  So  with a variable  the code will be cleaner    Maybe I want to much - or there s a type of local variable  Or using the type table and set data inside   What do you suggest me   Thank you

User · Answer

Here are some other approaches you can take.

1. CTE with union:

;WITH cte AS (SELECT a, b, c FROM table1)
SELECT a AS val FROM cte
UNION SELECT b AS val FROM cte
UNION SELECT c AS val FROM cte;

2. CTE with unpivot:

;WITH cte AS (SELECT a, b, c FROM table1)
SELECT DISTINCT val
FROM cte
UNPIVOT (val FOR col IN (a, b, c)) u;

User · Answer

Isn t this a much simpler solution  if I correctly understand the question  of course   I want to load email addresses that are in a table called  spam  into a variable    select email from spam   produces the following list  say    accountant  bid  buiilldanything com  club  cn  cricket  date  download  eu   To load into the variable  list   declare  list as varchar 8000  set  list     list  select email from spam     list may now be INSERTed into a table  etc    I hope this helps    To use it for a  csv file or in VB  spike the code   declare  list as varchar 8000  set  list     list  select     email      from spam  print  list   and it produces ready-made code to use elsewhere     accountant     bid     buiilldanything com     club     cn     cricket     date     download     eu     One can be very creative    Thanks  Nico

User · Answer

You can create table variables   DECLARE  result1 TABLE  a INT  b INT  c INT   INSERT INTO  result1 SELECT a  b  c FROM table1  SELECT a AS val FROM  result1 UNION SELECT b AS val FROM  result1 UNION SELECT c AS val FROM  result1   This should be fine for what you need

User · Answer

I came here with a similar question problem  but I only needed a single value to be stored from the query  not an array table of results as in the orig post   I was able to use the table method above for a single value  however I have stumbled upon an easier way to store a single value   declare  myVal int  set  myVal   isnull  select a from table1   0    Make sure to default the value in the isnull statement to a valid type for your variable  in my example the value in table1 that we re storing is an int

[sql-server-2005] SQL query, store result of SELECT in local variable

Examples related to sql-server-2005