CODEWITHSUNDEEP
sqlsql-server
Anup
Anup

Reputation: 9746

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

Upvotes: 1

Views: 60

Answers (4)

andrews
andrews

Reputation: 2173

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 ',' .

Upvotes: 1

Eric
Eric

Reputation: 5743

-- Prepare the data
DECLARE @EmployeeDetail TABLE (Id int identity(1,1), Code varchar(10))
INSERT @EmployeeDetail VALUES ('ABC1'), ('ABC2'), ('DEF'), ('ABC3')

DECLARE 
    @CodePattern varchar(10) = 'ABC',
    @RangeFrom int = 1,
    @RangeTo int = 200

DECLARE @Ids varchar(max) = (SELECT STUFF((
    SELECT ',' + CAST(Id AS varchar(10)) 
    FROM @EmployeeDetail 
    WHERE 
        -- The pattern of the code is prefix + number
        -- Can use LIKE and BETWEEN to replace your multiple query
        code LIKE @CodePattern + '%' 
        AND SUBSTRING(code, LEN(@CodePattern) + 1, 10) BETWEEN @RangeFrom AND @RangeTo
    FOR XML PATH('')
), 1, 1, ''))

PRINT @Ids

Upvotes: 0

sandeep rawat
sandeep rawat

Reputation: 4957

Question can distributed in two part

  1. Generate variable value .

  2. Filter result on data generated on step1.

    --Step 1

declare @num int
declare @text VARCHAR(MAX)

set @text = 'ABC'
set @num = 1

declare @results table ( val varchar(10) )

while (@num <= 200)
begin
  insert into @results ( val ) values (@text + cast(@num as varchar(3)) )
  set @num = @num + 1
end

--Step 2

Select ID from EmployeeDetails e
inner join @results r on r.val=e.Code

Upvotes: 0

Paresh J
Paresh J

Reputation: 2419

As far as my understanding you want to concatenate the result of all IDs with code as ABC1, ABC2, .. ABC200. Check my below solution and hope it helps you.

declare @num int
declare @text VARCHAR(MAX)

set @text = 'ABC'
set @num = 1

declare @results table ( val varchar(10) )

while (@num <= 200)
begin
  insert into @results ( val ) values (@text + cast(@num as varchar(3)) )
  set @num = @num + 1
end

Select ID from EmployeeDetail where Code in (Select val from @results)

Upvotes: 0

Related Questions