Complicated SQl query. - 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

SQL Server Development (2000)

Complicated SQl query.

Author

Topic

kalstrai
Starting Member

2 Posts

Posted - 2007-04-26 : 18:21:30

Hi everyone, I'm doing some work trying to develop an application that makes use of an off the shelf peice of software we've been using for years. Its SQL and all I really need to do is get one view working to get everything to come together. Heres the layout of the two tables I'm working with:

table1: (is the master table, holds most of the client info)
important fields: client_id(pk)

table2: (holds a list of all the information entered into the programs user defined fields)
important fields: client_id(fk), type_id, value

I need a view created that returns the values for each client_id, but only ones with specific type_ids... so basically if teh tables looked like this

t1: client_id
1

t2: client_id type_id value
1 2 Box 2345
1 3 Mary
1 89 singer

the view would look like this

view: client_id address(type2) name(type3) occupation(type3)
1 box 2345 Mary singer

the Value col names are static, and I know what ones I want and what their id's are.

The program is maximizer, and if anyone has any expierence creating custom reports with Crystal reports 11 from maximizer's user defined fields, that would be a fantastic help also.

Thanks in advance,

John

P.s. I apologize for the bad formatting, I just previewed it and none of my makeshift columns line up :S

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 18:36:46

select clientid,
max(case when typeid = 2 then value end) as address,
max(case when typeid = 3 then value end) as name,
max(case when typeid = 89 then value end) as occupation
from t2
group by clientid
order by clientid

Peter Larsson
Helsingborg, Sweden

kalstrai
Starting Member

2 Posts

Posted - 2007-04-27 : 10:44:07

This is excellent help! It seems I know just enough SQL to get myself into trouble. Thank you Sir!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources