'Referencing the outer query from the case of a subquery in SQL server 2014
I'm trying to build a stored procedure that transforms a lot of data from a very condensed table with a key to find any given information, to a wide table with all of the information in columns per group. This is to preprocess information and alleviate at point of use. I've structured my query like below.
Select distinct a.group_,
, (select value from mytable b where a.group = b.group) as Information 1
from mytable a
However, when I want to use a case statement, it breaks the reference to the outer query
Select distinct a.group_,
, (case
when (select value from mytable b where a.group = b.group) is not null -- this breaks
then (select value from mytable b where a.group = b.group)
else (select anothervalue from mytable b where a.group = b.group)
end ) as Information 1
from mytable a
I thought about a work around with a simple case to find if the value is null, and execute an else statement, but I found that my 'is not null' didn't work in the when statment, and I needed to reference the outer query anyways for the else. So ultimately, I need some method to be able to conditionally select values for one column and have it tied to the group that I'm trying to transform. Any help would be appreciated, thanks.
Edit: Below is an example. To clarify, I need to be able to conditionally select information from potentially multiple sources together into the same column for any given group. It's also going to be working on a large amount of data so I need it to be as minimally computationally intensive as possible. From this table, I will combine all of these groups in different combinations to have one line per collection of groups, with each group at least one column in the final table. It's a little more complicated than that, but that's the general idea.
if OBJECT_ID(N'tempdb..#tt1') is not null
drop table #tt1
declare @counter INT
set @counter = 1
create table #tt1 (group_ int, id1 int, id2 int, info varchar(10))
while (@counter <= 5)
begin
insert into #tt1 (group_, id1, id2, info)
select @counter, @counter, @counter, CONCAT('info ', cast(@counter as varchar(10)))
set @counter = @counter +1
end
set @counter = 1
while (@counter <= 5)
begin
insert into #tt1 (group_, id1, id2, info)
select @counter, @counter, @counter+1, CONCAT('info ', cast(@counter+5 as varchar(10)))
set @counter = @counter +1
end
select distinct a.group_,
(select info from #tt1 b where a.group_ = b.group_ and id1 = 1 and id2=2) as Group1Info6
from #tt1 a
--The above works fine
select distinct a.group_,
(case
when (select info from #tt1 b where A.group_ = b.group_ and id1=1 and id2 =2) is < 6
then (select info from #tt1 b where A.group_ = b.group_ and id1=1 and id2 =2)
else (select info from #tt1 b where A.group_ = b.group_ and id1=1 and id2=1)
end) as Group1info
from #tt1 a
--The above does not.
Edit2: My desired results would look something like this. In my actual data there are many group 1's, with many group 1 info columns.
group_ | Group1Info | Group2Info | Group3Info | Group4Info | Group5Info |
---|---|---|---|---|---|
1 | info | ||||
2 | info | ||||
3 | info | ||||
4 | info | ||||
5 | info |
Then I'll present the information more cleanly to the end user like this.
group_ | Group1Info | Group2Info | Group3Info | Group4Info | Group5Info |
---|---|---|---|---|---|
1-5 | info | info | info | info | info |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|