Crosstab Query - Roll up Row Headings to one row

[wmmcpherson]

I have a crosstab query that is pulling the following: All of the Column Heading values are text

ID	Benefit1	Notes	All Benefit	CO	CT	IDAHO	VT
10818-09-1	14-INPATIENT HOSP R&B		500/PER DAY/15/			31
10818-09-1	14-INPATIENT HOSP R&B		500/PER DAY/15/	CO Exclude Others
10818-09-1	15-INPATIENT HOSP ICU		1000/PER DAY/15/			31
10818-09-1	15-INPATIENT HOSP ICU		1000/PER DAY/15/	CO Exclude Others
10818-09-1	16-INPATIENT HOSP SUBSTANCE		500/PER DAY/15/			31
10818-09-1	16-INPATIENT HOSP SUBSTANCE		500/PER DAY/15/	CO Exclude Others	CO Exclude Others
10818-09-1	17-INPATIENT HOSP MH R&B		250/PER DAY/15/			31
10818-09-1	17-INPATIENT HOSP MH R&B		250/PER DAY/15/	CO Exclude Others
10818-09-1	17-INPATIENT HOSP MH R&B		250/PER DAY/15/				MH-Parity

How do I make it look like this? So that none of the Benefit1 rows are repeated and the column heading values are all shown on one line?

ID	Benefit1	Notes	All Benefit	CO	CT	IDAHO	VT
10818-09-1	14-INPATIENT HOSP R&B		500/PER DAY/15/	CO Exclude Others		31
10818-09-1	15-INPATIENT HOSP ICU		1000/PER DAY/15/	CO Exclude Others		31
10818-09-1	16-INPATIENT HOSP SUBSTANCE		500/PER DAY/15/	CO Exclude Others	CO Exclude Others	31
10818-09-1	17-INPATIENT HOSP MH R&B		250/PER DAY/15/	CO Exclude Others		31	MH-Parity

Thank you for any help.

[ranman256]

Q1, that is the base query for the Crosstab, should ignore null recs.
then the Crosstab will only have items with data.

[CJ_London]

what does the source table look like? It may be better to construct your query a different way.

Otherwise consider using/adapting the concatrelated function (google to find out more) to work off your crosstab in a separate query