如何利用Org_Extra_Attr元数据，将User_Attr_Values字段映射为自定义属性查询数据？

Hey there! Let's tackle this SQL mapping problem step by step. We need to translate the generic str1-str4 and bool1-bool4 fields in User_Attr_Values into the custom attribute names defined in Org_Extra_Attr for each organization. Below are two practical approaches depending on your desired output format.

Approach 1: Row-Based Output (One Attribute Per Row) #

This format lists each user's custom attribute as a separate row, which is flexible if organizations might add/remove attributes over time.

SELECT
    u.org_id,
    u.user_id,
    o.attr_name,
    -- Map the generic field to its value based on attr_path
    CASE
        WHEN o.attr_path IN ('str1', 'str2', 'str3', 'str4') THEN
            CASE o.attr_path
                WHEN 'str1' THEN u.str1
                WHEN 'str2' THEN u.str2
                WHEN 'str3' THEN u.str3
                WHEN 'str4' THEN u.str4
            END
        WHEN o.attr_path IN ('bool1', 'bool2', 'bool3', 'bool4') THEN
            -- Convert boolean to string for consistent value column type
            CAST(CASE o.attr_path
                WHEN 'bool1' THEN u.bool1
                WHEN 'bool2' THEN u.bool2
                WHEN 'bool3' THEN u.bool3
                WHEN 'bool4' THEN u.bool4
            END AS VARCHAR)
    END AS attr_value,
    -- Label the attribute type for clarity
    CASE WHEN o.attr_path LIKE 'str%' THEN 'string' ELSE 'boolean' END AS attr_type
FROM User_Attr_Values u
INNER JOIN Org_Extra_Attr o 
    ON u.org_id = o.org_id
ORDER BY u.org_id, u.user_id, o.attr_name;

Sample Output Snippet: #

Approach 2: Column-Based Output (One Attribute Per Column) #

If you prefer a flattened view where each custom attribute is a dedicated column (more readable for reporting), use this pivot-style query:

WITH AttributeMappings AS (
    SELECT
        u.org_id,
        u.user_id,
        o.attr_name,
        CASE
            WHEN o.attr_path LIKE 'str%' THEN
                CASE o.attr_path
                    WHEN 'str1' THEN u.str1
                    WHEN 'str2' THEN u.str2
                    WHEN 'str3' THEN u.str3
                    WHEN 'str4' THEN u.str4
                END
            WHEN o.attr_path LIKE 'bool%' THEN
                CAST(CASE o.attr_path
                    WHEN 'bool1' THEN u.bool1
                    WHEN 'bool2' THEN u.bool2
                    WHEN 'bool3' THEN u.bool3
                    WHEN 'bool4' THEN u.bool4
                END AS VARCHAR)
        END AS attr_value
    FROM User_Attr_Values u
    INNER JOIN Org_Extra_Attr o 
        ON u.org_id = o.org_id
)
SELECT
    org_id,
    user_id,
    MAX(CASE WHEN attr_name = 'desk_name' THEN attr_value END) AS desk_name,
    MAX(CASE WHEN attr_name = 'citizen' THEN attr_value END) AS citizen,
    MAX(CASE WHEN attr_name = 'perm_user' THEN attr_value END) AS perm_user,
    MAX(CASE WHEN attr_name = 'skype_id' THEN attr_value END) AS skype_id,
    MAX(CASE WHEN attr_name = 'twitter' THEN attr_value END) AS twitter
FROM AttributeMappings
GROUP BY org_id, user_id
ORDER BY org_id, user_id;

Sample Output Snippet: #

Quick Notes: #

• For the column-based approach, update the MAX(CASE...) clauses if new attribute names are added to Org_Extra_Attr.
• We cast boolean values to strings to keep a consistent data type in the attr_value column for row-based output.
• Swap INNER JOIN with LEFT JOIN if you want to include users even if their organization has no custom attributes defined.

内容的提问来源于stack exchange，提问作者Xavier DSouza