HI,

I have a requirement in my report where in i need to convert each of row from the db into a report column.

Example:

DB rows
--------
ID ID_value ID_TYPE
-------------------------------------
AAA 2h24h3j4 2440
AAA 35785jhrj 2441
AAA fjg897t89 2442
AAA kljgklj88 2443


So i need to identify for the same id for each of the different category id_type i have to have a separate column
like below:

ID column1 column2 column3 column4
------------------------------------------------
AAA 2h24h3j4 35785jhrj fjg897t89 kljgklj88

I need not display the ID_value,ID_TYPE and also ID.

Hence for a previous report , i had requirement wherein i need to convert only two rows as two columns.Hence
i used the hold file concept , where in i created two separate hold file with FORMAT FOCUS INDEX syntax and joined
both the hold files. But in this case i need to do this for atleast 12 to 14 rows. Is there any other way to do it ?

I had tried the below(EXAMPLE FOR TWO ROWS ALONE) :

  
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HLD_NCP
END


DEFINE FILE HLD_NCP
ID
State_License/A50= IF ID_TYPE_C EQ 2454 THEN PHARM_DEF_LOC_ID_I ELSE ' ';
NPI/A50 = IF ID_TYPE_C EQ 2451 THEN PHARM_DEF_LOC_ID_I ELSE '  ';
END

TABLE FILE HLD_NCP
SUM
PHARM_DEF_LOC_I
State_License
NPI
BY PHARM_DEF_LOC_I NOPRINT
END

Output is as follows:
-------------------------------------
PAGE 1

PHARM_DEF_LOC_I State_License NPI
AAA

Here Statelicense and NPI are blank.

When you change the SUM to PRINT , output is as follows:

PAGE 1

PHARM_DEF_LOC_I State_License NPI
AAA
AAA 28RS00562100
AAA 1396767901
AAA
AAA
AAA
AAA

I get only the last record. Ss this because the unique identifier is alphanumeric that i get only the
last record retrieved?

Whereas i need the following output:

PAGE1
------
PHARM_DEF_LOC_I State_License NPI
AAA 28RS00562100 1396767901

Appreciate your help

Thanks!
Jan

This message has been edited. Last edited by: Kerry,

Jan, have you looked into using the ACROSS phrase?

Jan,

Ginny has the right approach. Just beware that ACROSS is limited to 64 values.
Now, when you sort ACROSS, you want to number the occurences for each ID. WF provides a nice feature: LIST.

  
TABLE FILE CAR
LIST SALES SEATS
BY COUNTRY
ON TABLE HOLD
END
-RUN
DEFINE FILE HOLD
ALIST/A5=LJUST(5, FTOA(LIST, '(D5c)', 'A5'), 'A5');
COLS/A12='COLUMN' || ALIST;
END
TABLE FILE HOLD
SUM SALES SEATS
BY COUNTRY
ACROSS COLS AS ''
ON TABLE SET HTMLCSS ON
END

Jan, Danny,

The limit for ACROSS columns and values have been greatly increased. In 711 it was a maximum of 1024, later releases should support a total of 1056. This is all limited further by the maximum record length overall, which is 32 Kb.

Hi,

Using ACROSS i was able to resolve the issue,like below:

 
DEFINE FILE PHMSTR
State_License/I2= IF PHARM_DEF_LOC_I EQ LAST PHARM_DEF_LOC_I THEN State_License + 1 ELSE 1 ;
END

TABLE FILE PHMSTR
SUM
PHARM_DEF_LOC_ID_I
ACROSS State_License AS ''
BY Store
 

Thanks!
janani