p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Query Result (http://p2p.wrox.com/showthread.php?t=5646)

mateenmohd November 1st, 2003 03:53 AM

Query Result
 
table data
-----------
FromNode ToNode Volume
10011 10010 1353
10011 10012 47
10011 10018 243
10011 10012 330
10011 10014 377
10011 10016 976
10011 10018 310
10012 2755 1586
......

I have query like this.

select
case BOND when 'IP-1' then volume end as [IP-1],
case BOND when 'IP-9' then volume end as [IP-9],
case BOND when 'IP-12' then volume end as [IP-12],
case BOND when 'IP-41' then volume end as [IP-41],
case BOND when 'SBU' then volume end as SBU,
case BOND when 'SBL' then volume end as SBL,
case BOND when 'SBT' then volume end as SBT,
case BOND when 'SBR' then volume end as SBR
from
(select fromnode,tonode,volume,substring(fromnode,3,2) IP,
fromnode % 10 fromnode2, tonode % 10 tonode2,
case
when substring(fromnode,3,2)=01 then 'IP-1'
when substring(fromnode,3,2)=09 then 'IP-9'
when substring(fromnode,3,2)=12 then 'IP-12'
when substring(fromnode,3,2)=41 then 'IP-41'
when FromNode%10=1 and ToNode%10=2 then 'SBU'
when FromNode%10=1 and ToNode%10=4 then 'SBL'
when FromNode%10=1 and ToNode%10=6 then 'SBT'
when FromNode%10=1 and ToNode%10=8 then 'SBR'
end BOND
from emme) emme2
ORDER BY IP

Output in SQL analyzer like this.

IP-1 IP-9 IP-12 IP-41 SBU SBL SBT SBR
1353 NULL NULL NULL NULL NULL NULL NULL
NULL 47 NULL NULL NULL NULL NULL NULL
NULL NULL 243 NULL NULL NULL NULL NULL
NULL NULL NULL 1586 NULL NULL NULL NULL
NULL NULL NULL NULL 330 NULL NULL NULL
NULL NULL NULL NULL NULL 1711 NULL NULL
NULL NULL NULL NULL NULL NULL 1980 NULL
NULL NULL NULL NULL NULL NULL NULL 1310
…….
And so on…….

I want to display result by the query like this

        SBU SBL SBT SBR
IP-1 330 1711 1980 1310
IP-9 331 1710 1981 1311
IP-12 332 1712 1982 1312
IP-41 334 1713 1983 1313
…..
and so on……

what query (changes in above query) will be use

if IP-1 then display SBU,SBL,SBT,SBR volume
if IP-9 then display SBU,SBL,SBT,SBR volume
if IP-12 then display SBU,SBL,SBT,SBR volume
if IP-41 then display SBU,SBL,SBT,SBR volume
in front of IP-1 row..

or there is alternate procedure to display above result ?

Please help

Mateen


All times are GMT -4. The time now is 04:14 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.