Logic to buld the query

Transact-SQL
1

Hi Team,

I need a solution for the belo scenario.Please help.

I have a table like Switch
ID Col1 Col2 Col3
1001 Y N Y
1002 N N Y
1003 Y Y Y

and table Cross

Code Switch

50 Col1
22 Col2
12 Col3

I need a resul like below (Have to fetch the data from second table for the 'Y' values in the first table)
1001 50
1001 12
1002 12
1003 50
1003 22
1003 12

Thanks,
Binto

2

Try this:

Declare @Switch as table
(
id int,
col1 char(1),
col2 char(1),
col3 char(1)
);

Declare @Cross as table
(
code varchar(5),
switch varchar(5)
);

insert @Switch 
values
(1001,'Y','N','Y'),
(1002,'N','N','Y'),
(1003,'Y','Y','Y');

insert @Cross 
values
(50,'Col1'),
(22,'Col2'),
(12,'Col3');

select a.id
,c.code   
from @switch a
outer apply
(
values ('Col1', col1),('Col2',col2),('Col3',col3)
) b (switch, value)
inner join @Cross c
on c.switch = b.switch and b.value = 'Y';

Hope this helps!

3

hi

i know femiolan has given answer

this is another way of doing it
something different

which way for
performance .tuning .. coding .. decreasing logical reads etc is another story

hope it helps ..
if it helps great :slight_smile: :slight_smile:

i love feedback
thanks

drop create data 
drop table #cross 
go


drop table #switch 
go 

create table #switch 
(
ID int, 
Col1 varchar(1),
Col2 varchar(1),
Col3 varchar(1)
)
go 

insert into #switch select 1001, 'Y','N','Y'
insert into #switch select 1002, 'N','N','Y'
insert into #switch select 1003, 'Y','Y','Y'
go 

select * from #switch
go 
 

create table #cross 
(
code int ,
switch varchar(100)
)
go 

insert into #cross select 50,'Col1'
insert into #cross select 22,'Col2'
insert into #cross select 12,'Col3'
go 

select * from #cross
go
SQL .. 
;WITH cte 
     AS (SELECT *, 
                CASE 
                  WHEN col1 = 'Y' THEN 'Col1' 
                  ELSE 'x' 
                END AS Col1a, 
                CASE 
                  WHEN col2 = 'Y' THEN 'Col2' 
                  ELSE 'x' 
                END AS Col2a, 
                CASE 
                  WHEN col3 = 'Y' THEN 'Col3' 
                  ELSE 'x' 
                END AS Col3a 
         FROM   #switch) 
SELECT a.id, 
       b.code 
FROM   cte a 
       JOIN #cross b 
         ON a.col1a = b.switch 
             OR a.col2a = b.switch 
             OR a.col3a = b.switch

image

4

Thanks femiolan for your response.My requirement is in Sybase.Outer apply is giving syntax issue.Can you please help on this.

Once again Thanks a lot for your help.

5

Hi

Sorry for asking

What about my
Solution

No help to you

Mine
No need for outer apply

Just curious

6

Hi harishgg1,

I am sorry for the delay in reply.

Thanks for your help. I have more columns (around 20) in #Switch.So I need to write 20 case statements to achieve the same.Also this solution is for Sybase.I am not sure CTE will work in Sybase.

Appreciated your help.

Thanks,

7

As you might have figured out this is Microsoft SQL forum.
Try this forum

http://forums.tutorialized.com/sybase-114/

8

Hi binto

What are you looking for ,...

I mean 20 columns

I could put 20 columns and come up with your solution i mean what you want

What are you looking for

Thanks

9

Hi Harishgg1,

I am looking for a solution with minimal hardcoding.If switch table is having 25 columns (Col1,col2.....col25),it will be difficult to write 25 case statements.I am looking for a general solution.

Thanks,

10

Hi yosiasz,

Thanks ..
I think this forum is not active now.

Thanks,

11

One idea is to create a separate table with 1 column and populate that column with 25 rows..col1 to col25 and use that table in joins

No need for case statement s.
Alternates there

I can try
Please let me know

For senior experts
On this forum...it's a piece of cake

Thanks

12

not familiar with sysbase, but try unpivot...

select b.id 
,c.code
from 
	(
	select a.* 
	from @Switch a
	
	) p
unpivot
	(
	value for switch in (col1, col2, col3) 
	) as b
inner join @Cross c
on c.switch = b.switch and b.value = 'Y';