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






你可能感興趣的文章

metadata 、<meta> 知多少

metadata 、<meta> 知多少

SlowHTTPTest筆記-1

SlowHTTPTest筆記-1

[day 02] new & factory: 如何建立一個新物件

[day 02] new & factory: 如何建立一個新物件






留言討論