postgresql - Transposing columns into rows, with fixed row labels - Stack Overflow

admin管理员组

文章数量:1208153

Here is my code:

  table  as (

SELECT 

    day,
    district,
    uc,
    team_member,
    role_type,

    MAX(CASE WHEN text = 'AFP Cases Notified' THEN response_value END) AS AFP_Cases_Notified

    from finalset
where day=1
GROUP BY day, district, uc, team_member, role_type

)



SELECT 
    label,
    value
FROM 
    table,
    LATERAL (
        VALUES 
            ('day', "day"::text),
            ('district', "district"::text),
            ('uc', "uc"::text),
            ('team_member', "team_member"::text),
            ('role_type', "role_type"::text),
            ('afp_cases_notified', afp_cases_notified)

                ) AS t(label, value)
 

This has output like:

whereas what I want is output like:

i.e. the fixed row labels should only appear once in the rows. This can be done with a single transpose in excel, but I have tried many methods without success in postgres.

本文标签： postgresqlTransposing columns into rowswith fixed row labelsStack Overflow