Query Help

Hi All,
Hoping you might be able to help with this one.
Say I have this layout:
Tbl_People
Person_ID, Name, Address, City, State, Zip.
Tbl_Response
Response_ID, Person_ID, Q_ID (integer)
LU_Questions
Q_ID, Q_Label
So basically I have a lookup table with a bunch of question IDs. What I'm
really looking to get is data in this format:
Person_ID Q_Label1 QLabel2 QLabel3
---
5 Yes Yes
No
6 No Yes
Yes
Where I have one column for each record in LU_Questions. If a record exists
in Tbl_Response with that Person_ID and Q_ID, then it will return Yes, if
not, then No.
Any idea how I can do this?James
Can you post sample data to be tested?
"James" <minorkeys@.gmail.com> wrote in message
news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> Hoping you might be able to help with this one.
> Say I have this layout:
> Tbl_People
> Person_ID, Name, Address, City, State, Zip.
> Tbl_Response
> Response_ID, Person_ID, Q_ID (integer)
> LU_Questions
> Q_ID, Q_Label
>
> So basically I have a lookup table with a bunch of question IDs. What I'm
> really looking to get is data in this format:
> Person_ID Q_Label1 QLabel2 QLabel3
> ---
> 5 Yes Yes No
> 6 No Yes Yes
> Where I have one column for each record in LU_Questions. If a record
> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
> Yes, if not, then No.
> Any idea how I can do this?
>|||I'm not sure what format you'd be looking for it in, but something like:
Tbl_People:
Person_ID Name Address City
1 Bob 123 Street Madison
2 John 321 Ave Johnstown
Tbl_Response:
Person_ID Q_ID
1 1
1 2
2 1
2 2
2 4
Tbl_Questions:
Q_ID Q_Label
1 Golf
2 Camping
3 Hiking
4 Fishing
So ultimately I want:
Name OtherMetaData Golf Camping Hiking Fishing
Bob street/city/etc Yes Yes No No
John street/city/etc Yes Yes No Yes
Make sense? But it would need to be dynamic as Tbl_Questions grows.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
> James
> Can you post sample data to be tested?
>
>
> "James" <minorkeys@.gmail.com> wrote in message
> news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> Hoping you might be able to help with this one.
>> Say I have this layout:
>> Tbl_People
>> Person_ID, Name, Address, City, State, Zip.
>> Tbl_Response
>> Response_ID, Person_ID, Q_ID (integer)
>> LU_Questions
>> Q_ID, Q_Label
>>
>> So basically I have a lookup table with a bunch of question IDs. What
>> I'm really looking to get is data in this format:
>> Person_ID Q_Label1 QLabel2 QLabel3
>> ---
>> 5 Yes Yes No
>> 6 No Yes Yes
>> Where I have one column for each record in LU_Questions. If a record
>> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
>> Yes, if not, then No.
>> Any idea how I can do this?
>|||On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <u...@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>
> > James
> > Can you post sample data to be tested?
> > "James" <minork...@.gmail.com> wrote in message
> >news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
> >> Hi All,
> >> Hoping you might be able to help with this one.
> >> Say I have this layout:
> >> Tbl_People
> >> Person_ID, Name, Address, City, State, Zip.
> >> Tbl_Response
> >> Response_ID, Person_ID, Q_ID (integer)
> >> LU_Questions
> >> Q_ID, Q_Label
> >> So basically I have a lookup table with a bunch of question IDs. What
> >> I'm really looking to get is data in this format:
> >> Person_ID Q_Label1 QLabel2 QLabel3
> >> ---
> >> 5 Yes Yes No
> >> 6 No Yes Yes
> >> Where I have one column for each record in LU_Questions. If a record
> >> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
> >> Yes, if not, then No.
> >> Any idea how I can do this... Hide quoted text -
> - Show quoted text -
select
tp.name
, tp.other
, tq.Q_Label
, tr.golf
, tr.camping,
, tr.hiking
, tr.fishing
from tbl_people tp
join (
select person_id
,Q_id
,case when Q_id =1 then 'yes' else 'no' end golf
,case when Q_id =2 then 'yes' else 'no' end Camping
,case when Q_id =3 then 'yes' else 'no' end Hiking
,case when Q_id =4 then 'yes' else 'no' end Fishing
from tbl_response
)as tr on tp.Person_id =tr.Person_id|||James
> I'm not sure what format you'd be looking for it in, but something like:
That what I meant
create table Tbl_People (Person_ID int, Name varchar(10)
,Address varchar(20), City varchar(20))
insert into Tbl_People values (1,'Bob','123 Street','Madison')
insert into Tbl_People values (2,'John','321 Ave',' Johnstown')
create table Tbl_Response (Person_ID int,Q_ID int)
insert into Tbl_Response values (1,1)
insert into Tbl_Response values (1,2)
insert into Tbl_Response values (2,1)
insert into Tbl_Response values (2,2)
insert into Tbl_Response values (2,4)
create table Tbl_Questions (Q_ID int,Q_Label varchar(10))
insert into Tbl_Questions values (1,'Golf')
insert into Tbl_Questions values (2,'Camping')
insert into Tbl_Questions values (3,'Hiking')
insert into Tbl_Questions values (4,'Fishing')
select Name ,Address, City,max(case when Q_Label ='Golf' then 'yes' else
'no' end) as Golf,
max(case when Q_Label ='Camping' then 'yes' else
'no' end) as Camping,
max(case when Q_Label ='Hiking' then 'yes' else
'no' end) as Hiking,
max(case when Q_Label ='Fishing' then 'yes' else
'no' end) as Fishing
from Tbl_People tp join Tbl_Response tr
on tp.Person_ID=tr.Person_ID
join Tbl_Questions tq on tq.Q_ID=tr.Q_ID
group by Name ,Address, City
"James" <minorkeys@.gmail.com> wrote in message
news:%23Tx7Y6nsHHA.4916@.TK2MSFTNGP04.phx.gbl...
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>> James
>> Can you post sample data to be tested?
>>
>>
>> "James" <minorkeys@.gmail.com> wrote in message
>> news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> Hoping you might be able to help with this one.
>> Say I have this layout:
>> Tbl_People
>> Person_ID, Name, Address, City, State, Zip.
>> Tbl_Response
>> Response_ID, Person_ID, Q_ID (integer)
>> LU_Questions
>> Q_ID, Q_Label
>>
>> So basically I have a lookup table with a bunch of question IDs. What
>> I'm really looking to get is data in this format:
>> Person_ID Q_Label1 QLabel2 QLabel3
>> ---
>> 5 Yes Yes No
>> 6 No Yes Yes
>> Where I have one column for each record in LU_Questions. If a record
>> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
>> Yes, if not, then No.
>> Any idea how I can do this?
>>
>|||Thank you both. This is pretty close and I may end up having to work with
this.
Is there any way to make the column names dynamic, instead of hardcoded?
Either way thank you immensely for your help.
"schal" <shivaramchalla@.gmail.com> wrote in message
news:1182264456.599338.38270@.w5g2000hsg.googlegroups.com...
> On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
>> I'm not sure what format you'd be looking for it in, but something like:
>> Tbl_People:
>> Person_ID Name Address City
>> 1 Bob 123 Street Madison
>> 2 John 321 Ave Johnstown
>> Tbl_Response:
>> Person_ID Q_ID
>> 1 1
>> 1 2
>> 2 1
>> 2 2
>> 2 4
>> Tbl_Questions:
>> Q_ID Q_Label
>> 1 Golf
>> 2 Camping
>> 3 Hiking
>> 4 Fishing
>> So ultimately I want:
>> Name OtherMetaData Golf Camping Hiking Fishing
>> Bob street/city/etc Yes Yes No No
>> John street/city/etc Yes Yes No
>> Yes
>> Make sense? But it would need to be dynamic as Tbl_Questions grows.
>> "Uri Dimant" <u...@.iscar.co.il> wrote in message
>> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>>
>> > James
>> > Can you post sample data to be tested?
>> > "James" <minork...@.gmail.com> wrote in message
>> >news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> >> Hi All,
>> >> Hoping you might be able to help with this one.
>> >> Say I have this layout:
>> >> Tbl_People
>> >> Person_ID, Name, Address, City, State, Zip.
>> >> Tbl_Response
>> >> Response_ID, Person_ID, Q_ID (integer)
>> >> LU_Questions
>> >> Q_ID, Q_Label
>> >> So basically I have a lookup table with a bunch of question IDs. What
>> >> I'm really looking to get is data in this format:
>> >> Person_ID Q_Label1 QLabel2 QLabel3
>> >> ---
>> >> 5 Yes Yes No
>> >> 6 No Yes Yes
>> >> Where I have one column for each record in LU_Questions. If a record
>> >> exists in Tbl_Response with that Person_ID and Q_ID, then it will
>> >> return
>> >> Yes, if not, then No.
>> >> Any idea how I can do this... Hide quoted text -
>> - Show quoted text -
> select
> tp.name
> , tp.other
> , tq.Q_Label
> , tr.golf
> , tr.camping,
> , tr.hiking
> , tr.fishing
> from tbl_people tp
> join (
> select person_id
> ,Q_id
> ,case when Q_id =1 then 'yes' else 'no' end golf
> ,case when Q_id =2 then 'yes' else 'no' end Camping
> ,case when Q_id =3 then 'yes' else 'no' end Hiking
> ,case when Q_id =4 then 'yes' else 'no' end Fishing
> from tbl_response
> )as tr on tp.Person_id =tr.Person_id
>