Help with a Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with a Select

Author

Topic

slserra
Starting Member

19 Posts

Posted - 2003-10-05 : 13:35:12

I have the following tables:

tbl_ResponseLayout
FieldId Title
------- ------------
1 Name
2 Address
3 Phone

tbl_Responses
RespId FieldId FieldValue
------ ------- ----------
1 1 Steve
1 2 1 Main St
1 3 555-1212
2 2 100 Center St

I'm having trouble creating a query that returns all three layout fields for each response. If a response exists for the particular field show the response otherwise return a null value. The output table would look like this:

RespId FieldId FieldValue
------ ------- ----------
1 1 Steve
1 2 1 Main St
1 3 555-1212
2 1 NULL
2 2 100 Center St.
2 3 NULL

How can this be done in SQL?

Thanks for you help.

Steve

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-05 : 14:47:59

select r.RespId, l.FieldId, r2.FieldValue
from tbl_ResponseLayout l
cross join (select distinct RespId from tbl_Responses) r
left outer join tbl_Responses r2
on r2.respId = r.respId
and r2.fieldId = l.fieldId
order by r.RespId, l.FieldId

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

slserra
Starting Member

19 Posts

Posted - 2003-10-05 : 17:48:41

Thanks! This is exactly what I needed.

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources