June 23, 2004 at 6:03 am
Hello!
I have a problem, look at this TableA and read my question the after
Account ManTyp Document Portvalue
100040 MCS ID Document 425.2200
100040 MCS Utiltity Bill 425.2200
100085 MCS ID Document 2582.6200
1000504 MCS ID Document 36086.6100
1000504 MCS Utiltity Bill 36086.6100
1000512 MCS ID Document 195713.2600
1000512 MCS Utiltity Bill 195713.2600
1000555 MCS Utiltity Bill 22223.6200
I want to return one record per account in a table
Account ManTyp Document1 Document2 Portvalue
100040 MCS ID Document Utiltity Bill 425.2200
100085 MCS ID Document 2582.6200
1000504 MCS ID Document Utiltity Bill 36086.6100
1000512 MCS ID Document Utiltity Bill 195713.2600
1000555 MCS Utiltity Bill 22223.6200
In otherwords i want column document to be divided into to colums, document1(to contain ID Documents) and document2 (to contain Utility Bill)
eg
struggling. please help with syntax
June 23, 2004 at 6:44 am
SELECT d.Account, d.ManTyp,
ISNULL(a.Document,'') AS [Document1],
ISNULL(b.Document,'') AS [Document2],
COALESCE(b.Portvalue,a.Portvalue) AS [Portvalue]
FROM (
SELECT DISTINCT Account, ManTyp
FROM
) d
LEFT OUTER JOIN
a
ON a.Account = d.Account
AND a.ManTyp = d.ManTyp
AND a.Document = 'ID Document'
LEFT OUTER JOIN
b
ON b.Account = d.Account
AND b.ManTyp = d.ManTyp
AND b.Document = 'Utiltity Bill'
ORDER BY d.Account, d.ManTyp
Far away is close at hand in the images of elsewhere.
Anon.
June 23, 2004 at 7:46 am
Thanks, this works perfect
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply