Need help with 4-table view - 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 2005 Forums

Transact-SQL (2005)

Need help with 4-table view

Author

Topic

sjb19020
Starting Member

6 Posts

Posted - 2008-09-18 : 10:13:36

Hi, I have a very difficult view I need to create for work. Here's what I can tell you, as info is proprietary. There are 4 tables involved, the first 3 are connected by the same key, and table 3 and 4 are connected by another key.

Here is the issue. Table 4 has item.type as numbers and item.name as its description. The boss wants The description listed. There are 15 types of item.type, and what I need to do is say when the item.type is indicated, show the item.name. I can nomally do this using MAX CASE but they want all the item.name output in one column!

So far, here's what I've figured out.

Select table1.field1, table1.field2, table1.field3,
MAX(CASE WHEN table2.field 1 = 0 THEN table2.field2 END)AS Alias1,
MAX(CASE WHEN table2.field 1 = 1 THEN table2.field2 END) AS Alias2,

From table1 INNER JOIN
table 2 ON table1.primary_key = table2.foreign_key
INNER JOIN
table3 ON table1.primary_key = table3.foreign_key
INNER JOIN
table4 ON table3.field1 = table4.field1

Group By table1.field1, table1.field2, table1.field3
Order By table1.field1, table1.field2, table1.field3

Please help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 10:19:50

Can you show sample output please?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-18 : 10:31:53

quote:
I can nomally do this using MAX CASE but they want all the item.name output in one column!

maybe this is what you are looking for http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

KH
[spoiler]Time is always against us[/spoiler]

sjb19020
Starting Member

6 Posts

Posted - 2008-09-18 : 10:36:46

Visakh,

If I used case for the above code here's what it would look like.

LastName FirstName ID Alias1 Alias2
Adams John 1 <value> <value>

Now if I added in the 15 cases using MAX CASE, here's where I would run into problems. It would only list Table4.item if the case matched the items. There would be 15 extra columns going from left to right, and only the data matching the criteria would be populated, meaning there's a ton of NULL data.

LastName FirstName ID Alias1 Alias2 Table4.Item
Adams John 1 <value> <value> NULL

The output would be that all of the table4.type and table4.item output is MOVED INTO a single column, AND still have it grouped by LastName, FirstName, ID, Alias1 and Alias2.

sjb19020
Starting Member

6 Posts

Posted - 2008-09-18 : 11:02:13

Khtan,

I am having trouble understanding the OP from your example thread. I understand the concept of using a temp table, but I'm confused as to the exact syntax he means by saying the words "stuff" and s1. Looking at it, I'm not sure it will work with the rest of the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 12:00:04

quote:
Originally posted by sjb19020

Visakh,

If I used case for the above code here's what it would look like.

LastName FirstName ID Alias1 Alias2
Adams John 1 <value> <value>

Now if I added in the 15 cases using MAX CASE, here's where I would run into problems. It would only list Table4.item if the case matched the items. There would be 15 extra columns going from left to right, and only the data matching the criteria would be populated, meaning there's a ton of NULL data.

LastName FirstName ID Alias1 Alias2 Table4.Item
Adams John 1 <value> <value> NULL

The output would be that all of the table4.type and table4.item output is MOVED INTO a single column, AND still have it grouped by LastName, FirstName, ID, Alias1 and Alias2.

ok. and what will be your table data like?

sjb19020
Starting Member

6 Posts

Posted - 2008-09-18 : 12:43:15

I can not show you real data output with the 15 columns because my work won't let me. My earlier reply shows how the columns after the Alias2 will contain NULL values if the case does not match the criteria.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 12:52:13

quote:
Originally posted by sjb19020

I can not show you real data output with the 15 columns because my work won't let me. My earlier reply shows how the columns after the Alias2 will contain NULL values if the case does not match the criteria.

dont post real data. just post sample format with hypothetical values like value1,value2,...
i just wanted to see how it appears in your table.

sjb19020
Starting Member

6 Posts

Posted - 2008-09-18 : 13:11:32

I cant set tabs right with this message board, and it won't accept white space. Annoying, but here goes.

LastName FirstName ID Location Position ItemName1 ItemName2 ItemName3 ItemName4
Adams John 249393 Boston Patriot NULL NULL VicePresident NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 13:14:52

quote:
Originally posted by sjb19020

LastName FirstName ID Alias1 Alias2
Adams John 1 <value> <value>

ok so this is how data exists in your table and you want to concatenate them to single column. is that what you're asking for?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources