Problem with Views! Updated - 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)

Problem with Views! Updated

Author

Topic

mibheenick
Starting Member

12 Posts

Posted - 2005-08-26 : 06:50:45

Hi everyone out there!
plz help am really tired looking for a solution to this!

I have a View(vw_A) which return me values like the following:

Num1 Typ1 Num2 Typ2 Num3 Typ3
---- ---- ---- ---- ---- ----
570 1 382 2 113 3
571 1 382 2 113 3
572 1 383 2 113 3

I want to retrieve the column values in different rows as follows:

case 1: Select * from vw_A where Num1 = 570 and Typ1 = 1
EXPECTED result:
Num1 Typ1 NumX TypX
---- ---- ---- ----
570 1 382 2
570 1 113 3

case 2: Select * from vw_A where Num2 = 382 and Typ2 = 2
EXPECTED result:
Num2 Typ2 NumX TypX
---- ---- ---- ----
382 2 570 1
382 2 571 1
382 2 113 3

case 3: Select * from vw_A where Num3 = 113 and Typ3 = 3
EXPECTED result:
Num3 Typ3 NumX TypX
---- ---- ---- ----
113 3 570 1
113 3 571 1
113 3 572 1
113 3 382 2
113 3 383 2

I have to get these results in another View, so I cant use Temptables. Anyone plz help me out of this! Need to complete this at the earliest.

Thanks in advance....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 07:01:52

You can write query based on the view

Select Num1,Typ1, Num2, Typ2 from vw_A where Num1=570
Union all
Select Num1,Typ2, Num3, Typ3 from vw_A where Num1=570

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-28 : 11:02:43

try some thing like this :

select * from (
select NUM1,TYPE1,NUM2,TYPE2 from vw_A
union
select NUM1,TYPE1,NUM3,TYPE3 from vw_A
union
select NUM2,TYPE2,NUM1,TYPE1 from vw_A
union
select NUM2,TYPE2,NUM3,TYPE3 from vw_A
union
select NUM3,TYPE3,NUM2,TYPE2 from vw_A
union
select NUM3,TYPE3,NUM1,TYPE1 from vw_A
) as x
where NUM1 = 570 and Type1 = 1

change the value of num1 in the query to 382, and type1 = 2 and then 113, and type1 = 3

you will see the result that are asking for

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources