convert row to column


member_id property_name value
1 p1 v1
1 p2 v2
1 p3 v3
1 p4 v4
1 p5 v5
2 p1 v6
2 p2 v7

member_id p1 p2 p3 p4 p5
1 v1 v2 v3 v4 v5
2 v6 v7 NULL NULL NULL

Window Function

SELECT
    distinct member_id,
    max(
      CASE
        WHEN (property_name = 'p1') THEN value
        ELSE NULL
      END
    ) OVER (PARTITION BY member_id) AS p1,
    max(
      CASE
        WHEN (property_name = 'p2') THEN value
        ELSE NULL
      END
    ) OVER (PARTITION BY member_id) AS p2,
    max(
      CASE
        WHEN (property_name = 'p3') THEN value
        ELSE NULL
      END
    ) OVER (PARTITION BY member_id) AS p3,
    max(
      CASE
        WHEN (property_name = 'p4') THEN value
        ELSE NULL
      END
    ) OVER (PARTITION BY member_id) AS p4,
    max(
      CASE
        WHEN (property_name = 'p5') THEN value
        ELSE NULL
      END
    ) OVER (PARTITION BY member_id) AS p5,
  FROM
    table

Group By

SELECT
  member_id,
  max(
    CASE
      WHEN (property_name = 'p1') THEN value
      ELSE NULL
    END
  ) AS p1,
  max(
    CASE
      WHEN (property_name = 'p2') THEN value
      ELSE NULL
    END
  ) AS p2,
  max(
    CASE
      WHEN (property_name = 'p3') THEN value
      ELSE NULL
    END
  ) AS p3,
  max(
    CASE
      WHEN (property_name = 'p4') THEN value
      ELSE NULL
    END
  ) AS p4,
  max(
    CASE
      WHEN (property_name = 'p5') THEN value
      ELSE NULL
    END
  ) AS p5,
FROM
    table
GROUP BY member_id
#Group By #Window Function #postgresql






Related Posts

簡明約耳續談軟體(More Joel on Software)導讀書摘

簡明約耳續談軟體(More Joel on Software)導讀書摘

[ week 1 ] Vim 編輯器 筆記

[ week 1 ] Vim 編輯器 筆記

Day04 你知道 setTimout、setInterval、requestAnimationFrame API 三者的關係嗎

Day04 你知道 setTimout、setInterval、requestAnimationFrame API 三者的關係嗎




Sponsored



Comments