query format

[wfd1bdb]

i AM RUNNING THIS CODE AND GETTING THE below results...the problem is I can't use the data because there too many fields for each Account number. I need 1 account number and have all the varcode fields follow it.

SELECT COMPANY, ACCT, SECTION, VARCODE, VARTEXT, REPORTED
FROM CLDICTIONARY AS A
WHERE (SECTION = 'FACTRPT') AND (VARCODE IN 'MONTH', 'DCDATE', 'IDDT', 'SE', 'ACT', 'IMAR', 'FP', 'DHMLSS', 'THMLSS', 'DJAIL', 'TJAIL', 'DIP', 'TIP', 'DSAIP', 'TSAIP', 'EMPLOY', 'LEMPLOY', 'SASTAGE', 'LIVING', 'EDUC', 'HIGHEDU')) AND (ACCT LIKE '102803') AND (REPORTED BETWEEN
'2006' + '-' + 'december' + '-' + '01' AND '2006' + '-' + 'december' + '-' + '31')
ORDER BY ACCT, REPORTED, VARCODE

Acct Section VARCODE VARTEXT REPORTED
989898 FACTRPT ACT Enrolled 12/1/2006
989898 FACTRPT DHMLSS 0 12/1/2006
989898 FACTRPT DIP 0 12/1/2006
989898 FACTRPT DJAIL 0 12/1/2006
989898 FACTRPT DSAIP 0 12/1/2006
……… …… ……. ….. ……..
………

I need it in the following format.
EXAMPLE
ACCT Section VARCODE/VARTEXT REPORTED
989898 FACTRPT, (ACT/ENROLLED), (DHMLSS/0), (DIP/0), (DJAIL/0), (DSAIP/0)12/1/2006
989899 FACTRPT, (ACT/NOT ENROLLED), (DHMLSS/1), (DIP/12),(DJAIL/0), (DSAIP/0)12/1/2006

I am not sure this is even possible. Any help would be appreciated!

BDB

 

[gmmastros]

You situation is a bit more complicated than the example in this thread: thread183-1159740 but take a look. The answer to your problem is in there.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom