Anup Anup - 9 days ago 5
SQL Question

SQL - Consolidate results of multiple queries in 1 variable or column

I want to consolidate results of multiple queries in 1 column or variable.
I have this Query :-

select Id from EmployeeDetail where Code = 'ABC1'
select Id from EmployeeDetail where Code = 'ABC2'

... So On till ABC200


I want all the Ids in a 1 variable to use it further.

I am trying to use foreach to get this. How to get this in a 1 variable to use it in further different query.

I have tried for example the below to get a string in 1 Concatenating variable :- Below code was just a trial, it is not woking on actual query.

declare @num int
declare @num1 VARCHAR(MAX)

set @num = 1

declare @results table ( val int )

while (@num < 84)
begin
insert into @results ( val ) values ( @num )
set @num = @num + 1
set @num1 += '5000'
select @num1
end

Answer

If the EmployeeDetail codes your are fetching all match some pattern, then you can achieve what you want using the simple query below:

declare @AllIDs varchar(max)
set @AllIDs = ''

select 
    @AllIDs = (@AllIDs + cast(ID as varchar(10)) + ',') 
from EmployeeDetail WHERE Code like 'ABC%'

After you run it, variable @AllIDs will contain all the IDs separated with ',' .