0

I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.

  with checked_adgroup AS (
     SELECT 
        ua.new_adgroup,
        ua.account,
        ua.campaign,
        ua.ad_group,
        ua."position",
        cp.category,
        pt.full_value,

       FROM unnest_adgroup ua
         LEFT JOIN taxonomy_category cp ON ua."position" = cp."position"
         LEFT JOIN taxonomy pt ON ua.short_val = pt.short_value AND cp.category = pt.category AND (pt.lob IS NULL OR pt.lob = ua.lob)
    )

SELECT * 
from crosstab(
'select
cad.account,
cad.campaign,
cad.ad_group,
cad.category,
cad.full_value
FROM checked_adgroup cad 
WHERE  cad.all_correct AND cad.category IS NOT NULL
ORDER BY 1,2,3')
AS final_result( 
 account text, campaign text, ad_group text,
 division text, lob text, match_type text  )
;

Error message:

ERROR: relation "checked_adgroup" does not exist LINE 7: FROM checked_adgroup cad

Output of checked_adgroup cte looks like below enter image description here

Desired output of the final statement is:

enter image description here

1
  • I wouldn't use the crosstab function to begin with. This is typically a lot easier with filtered aggregation. May 3 '20 at 15:01
0

The CTE is not visible inside the crosstab() function. You have to pass the complete query string including the CTE.

Possible alternative: create a temporary table instead of the CTE.

However, there are more problems in your query. all_correct seems undefined. Etc.

Basic instructions:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.