July 15, 2010 at 5:19 am
Hi
Hi need to conver the following:
VoyNo Sco
1234 ABC
1201 ABC
1201 EFG
into the format
1234 ABC NULL
1201 ABC EFG
Any ideas?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 15, 2010 at 5:37 am
right,
I've come up with this t-SQL to give me almost what I need:
SELECTDISTINCT
s1.voyno
,s1.sco as [sco1]
,s2.sco as [sco2]
,s3.sco as [sco3]
,s4.sco as [sco4]
,s5.sco as [sco5]
,s6.sco as [sco6]
,s7.sco as [sco7]
fromdbo.VoyageSCOss1
LEFT OUTER JOINdbo.VoyageSCOss2
ons1.voyno=s2.voyno
ANDs1.sco!=s2.sco
LEFT OUTER JOINdbo.VoyageSCOss3
ons1.voyno=s3.voyno
ANDs1.sco!=s3.sco
ANDs2.sco!=s3.sco
LEFT OUTER JOINdbo.VoyageSCOss4
ons1.voyno=s4.voyno
ANDs1.sco!=s4.sco
ANDs2.sco!=s4.sco
ANDs3.sco!=s4.sco
LEFT OUTER JOINdbo.VoyageSCOss5
ons1.voyno=s5.voyno
ANDs1.sco!=s5.sco
ANDs2.sco!=s5.sco
ANDs3.sco!=s5.sco
ANDs4.sco!=s5.sco
LEFT OUTER JOINdbo.VoyageSCOss6
ons1.voyno=s6.voyno
ANDs1.sco!=s6.sco
ANDs2.sco!=s6.sco
ANDs3.sco!=s6.sco
ANDs4.sco!=s6.sco
ANDs5.sco!=s6.sco
LEFT OUTER JOINdbo.VoyageSCOss7
ons1.voyno=s7.voyno
ANDs1.sco!=s7.sco
ANDs2.sco!=s7.sco
ANDs3.sco!=s7.sco
ANDs4.sco!=s7.sco
ANDs5.sco!=s7.sco
ANDs6.sco!=s7.sco
--WHERE s1.voyno not like '000%'
WHERE s1.voyno = '4699'
order BY
s1.voyno
,s1.sco
,s2.sco
,s3.sco
,s4.sco
,s5.sco
,s6.sco
,s7.sco
Giving me:
[font="Courier New"]voyno sco1 sco2 sco3 sco4 sco5 sco6 sco7
----- ---- ---- ---- ---- ---- ---- ----
4699 GNS HAP NULL NULL NULL NULL NULL
4699 HAP GNS NULL NULL NULL NULL NULL
[/font]
I need to some how make these two rows into just a single row. Any ideas ?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply