Different levels in one record not in many records

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)

Different levels in one record not in many records

Author

Topic

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-21 : 15:37:08

Hi Guys,
This is the output of one view but this view has the info of 3 tables.I am able to fetch all the info i wanted from the three tables but right now i am stuck since i don't want to see different records if it has 2 levels.RIght now i am showing 2 levels(last two colums though it has five levels)First level is MIDATL and second is PA and their description is in the first column like Mid Atlantic and Pennsylvania.I don't want to see two records for this i want to see that it should show as Mid Atlantic /Pennsylvania in one column only.so in my output in place of 8 records i should show 6 records.Hope i am clear

Sleeping Bags KAMP RS 7 2 XSB
Absorbents CWEB G1 6041 2 6041
Sports Gear KAMP RS 8 1 XSG
Mid Atlantic KAMP MAIN 14 1 MIDATL PA
Pennsylvania KAMP MAIN 14 1 MIDATL PA
Delaware KAMP MAIN 15 1 MIDATL DE
Mid Atlantic KAMP MAIN 15 1 MIDATL DE
Containment Vessels CWEB G1 6033 1 6033
Thanks!

BCrowe
Starting Member

23 Posts

Posted - 2004-10-22 : 00:19:00

The fact that noone has responded should indicate that like me noone understand what your were asking...try clarifying. What is a level?

BCrowe

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 08:25:07

could it be that all you need is:
select col1 + '/' + Col2 as Together, ...
from ...

col1 has the value MidAtlantic
col2 has the value Pennsylvania

Go with the flow & have fun! Else fight the flow

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-22 : 10:14:52

Yes Spirit,You are partially right but i don't want these two records i want one record with two levels like Mid Atlantic and Delaware.How should i specify that which coulmn and from which record i want to see together. Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 10:34:23

so you acctually want the first of the group (when grouping by something)???

i'm wild guessing here because it's hard to grasp what you acctually need.

Go with the flow & have fun! Else fight the flow

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-23 : 00:05:38

Hi Guys,
This is the output of two views but i would be using 5 views.My third,fourth and fifth view doesn't have any values at this time but will have in the future.Like in this case ,if you look at fourth record you will see two levels like Mid Atlantic as first level and Delaware as second level but if there are any values it should show as Mid Atlantic/Delaware/whatever/whatever/whatever in one column.You can see from the output that last three levels are blank.My five views gives five levels.so if i use two levels i get this output using the following code:

select desc1_view.desc1 as Combined_Description,desc1_view.pub_id,desc1_view.section_id,desc1_view.category_id,desc1_view.numberofTimes,desc1_view.level1,desc1_view.level2,desc1_view.level3,desc1_view.level4,desc1_view.level5 from desc1_view
where level2=''AND level3=''AND level4='' AND level5=''
UNION
select desc1_view.desc1 + '/' + desc2_view.desc2 as Combined_Description,desc1_view.pub_id,desc1_view.section_id,desc1_view.category_id,desc1_view.numberofTimes,desc1_view.level1,desc1_view.level2,desc1_view.level3,desc1_view.level4,desc1_view.level5 from desc1_view,desc2_view
where desc1_view.level1=desc2_view.level1
AND
desc1_view.level2=desc2_view.level2

OUTPUT:
Absorbents |CWEB|G1 |6041|6041 | | | | |
Sleeping Bags |KAMP|RS |7 |XSB | | | | |
Containment Vessels |CWEB|G1 |6033|6033 | | | | |
Mid Atlantic/Delaware |KAMP|MAIN|15 |MIDATL|DE| | | |
Mid Atlantic/Pennsylvania|KAMP|MAIN|14 |MIDATL|PE| | | |
Sports Gear |KAMP|RS |8 |XSG | | | | |

But if i use desc3_view,desc4_view,desc5_view then i don't get this output.It shows me nothing.I want that it should be same output since there are no values but using all the five views not two.I hope i am clear.I don't know why it is not working may be because of null values.I wanna select like this
select desc1_view.desc1 + '/' + desc2_view.desc2+ '/' + desc3_view.desc3 + '/' + desc4_view.desc4+ '/' + desc5_view.desc5 From desc1_view,desc2_view,desc3_view,desc4_view,desc5_view,

Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources