problem with the text feild datatype.

[garry1]

Hi,

I am trying to transpose the values from columns into rows and create a report and I am having a problem with the group by clause because of the text feild. If someone can suggest any other alternate on this I would really apprecaite it. When I try doing the union I don't get any comments in the result.

My query is;

SELECT risk_competitive_program.grantee_name,
convert(varchar 55),risk_competitive_exception.c_excep_comment) // text feild,
risk_competitive_exception.c_excep_code,
risk_competitive_exception.management_rep,
risk_competitive_program.full_grantee_id,
max(CASE risk_competitive_program.prg_type WHEN 'BEDI' THEN risk_competitive_program.rcp_grand_total_score END ) AS BEDI,
max(CASE risk_competitive_program.prg_type WHEN 'Colonias' THEN risk_competitive_program.rcp_grand_total_score END ) AS Colonias,
max(CASE risk_competitive_program.prg_type WHEN 'EDI' THEN risk_competitive_program.rcp_grand_total_score END ) AS EDI,
max(CASE risk_competitive_program.prg_type WHEN 'HBCU' THEN risk_competitive_program.rcp_grand_total_score END ) AS HBCU,
max(CASE risk_competitive_program.prg_type WHEN 'HOPWA Competitive' THEN risk_competitive_program.rcp_grand_total_score END ) AS HOPWACompetitive,
max(CASE risk_competitive_program.prg_type WHEN 'RHED' THEN risk_competitive_program.rcp_grand_total_score END ) AS RHED,
max(CASE risk_competitive_program.prg_type WHEN 'Round II EZs' THEN risk_competitive_program.rcp_grand_total_score END ) AS RoundIIEZs,
max(CASE risk_competitive_program.prg_type WHEN 'Sec 8 SRO Mod.Rehab' THEN risk_competitive_program.rcp_grand_total_score END ) AS Sec8SROModRehab,
max(CASE risk_competitive_program.prg_type WHEN 'S + C' THEN risk_competitive_program.rcp_grand_total_score END ) AS SC,
max(CASE risk_competitive_program.prg_type WHEN 'SHP' THEN risk_competitive_program.rcp_grand_total_score END ) AS SHP,
max(CASE risk_competitive_program.prg_type WHEN 'Small Cities Comp' THEN risk_competitive_program.rcp_grand_total_score END ) AS SmallCitiesComp,
max(CASE risk_competitive_program.prg_type WHEN 'Youthbuild' THEN risk_competitive_program.rcp_grand_total_score END ) AS Youthbuild
FROM risk_competitive_program,
risk_competitive_exception
where risk_competitive_exception.full_grantee_id = risk_competitive_program.full_grantee_id ) and
( risk_competitive_program.prg_yr = '2002')
group by risk_competitive_program.grantee_name,c_excep_comment,
risk_competitive_exception.c_excep_code,
risk_competitive_exception.management_rep,
risk_competitive_program.full_grantee_id,
risk_competitive_program.rcp_grand_total_score,
risk_competitive_program.prg_type;

Any help is very much appreciated on this.

 

[TerryStover]

Do the crosstab on risk_competitive_program in a subquery first to get the columns, in the outer query join in the risk_competitive_exception.