Get the fields from a table with the same value

Transact-SQL
1

I have a table like this:
lastname_1 lastname_2 lastname_3 lastname 4 legalguardian_1 legalguardian_2 legalguardian_3 legalguardian_4

Patrick Anderson James Smith 1 0 1 0

Now I want to query this so that Patrick and James are returned because the values on their corresponding legalguardian fields are 1.

Thanks for your help.

2

If I understood your request correctly:

Select lastname_1, lastname_2, lastname_3, lastname 4, legalguardian_1, legalguardian_2, legalguardian_3 ,legalguardian_4
From yourTable
Where legalguardian =1

3
  1. Always provide real data for us to help you in the following format. otherwise folks wont help you or it will take a lot of back and forth to answer your question.
create table #guardianship(lastname_1 varchar(50), lastname_2 varchar(50), 
lastname_3 varchar(50), lastname_4 varchar(50), 
legalguardian_1 int, legalguardian_2 int, 
legalguardian_3 int, legalguardian_4 int)
insert into #guardianship
select 'Patrick', 'Anderson', 'James', 'Smith',  1, 0, 1, 0

select *
From #guardianship
Where legalguardian_1 =1 or legalguardian_2 = 1
or legalguardian_3 = 1
or legalguardian_4 = 1
  1. would you accept a change in your design as well? Because what happens if tomorrow there are 5 more guardians?
1 Like
4

wonky solution for unusual table design

create table #guardianship(lastname_1 varchar(50), lastname_2 varchar(50), lastname_3 varchar(50), lastname_4 varchar(50), legalguardian_1 int, legalguardian_2 int, legalguardian_3 int, legalguardian_4 int)
insert into #guardianship
select 'Patrick', 'Anderson', 'James', 'Smith',  1, 0, 1, 0

create table #people(peoplename varchar(50), joinfunk varchar(5))
create table #guardians( guardianValue int, joinfunk varchar(5))

insert into #people
	select 
	  
	  indicatorvalue,
	   
	  substring(indicatorname,  CHARINDEX('_',indicatorname,0), len(indicatorname)) joinfunk
	from #guardianship
	unpivot
	(
	  indicatorvalue
	  for indicatorname in (lastname_1, lastname_2, lastname_3, lastname_4)
	) unpiv;
	go

	insert into #guardians
	select indicatorvalue,	   
	  substring(indicatorname,  CHARINDEX('_',indicatorname,0), len(indicatorname)) joinfunk
	from #guardianship
unpivot
(
	indicatorvalue
	for indicatorname in (legalguardian_1, legalguardian_2, legalguardian_3, legalguardian_4)
) unpiv


select peoplename, guardianValue
from #people p
join #guardians g on p.joinfunk = g.joinfunk
where guardianValue = 1


drop table #guardianship
drop table #guardians
drop table #people