Why do i have duplicate when i do a pivot table in microsoft sql database

Question

Can anybody tell me what is wrong with my query? I keep getting duplicate in my result which should'nt be the case. i should be expecting a result of the following:

post_id| post_impressions | likes | comments | shares | post_video_views | post_video_views_organic
123    | 500              | 500    | 700     | 750    | 3500             | 2800 

---

However, i am getting a result of this.

post_id| post_impressions | likes | comments | shares | post_video_views | post_video_views_organic
123    | 500              | 500   | 700      | 750    | 3500             | 2800  
123    | 500              | null  | null     | null   | 3500             | 2800

---

This is my query right here.

    SELECT post_id, post_impressions, likes, comments, shares, post_video_views, post_video_views_organic
    FROM
    (
    SELECT post_id, metric_name, cast(metric_value as numeric) as metric_value
      FROM fb_table
      WHERE
      metric_name in ('post_impressions', 'likes', 'comments', 'shares', 'post_video_views', 'post_video_views_organic')
    
    ) A
    PIVOT
    (
      MAX(metric_value)
      FOR metric_name in (post_impressions, likes, comments, shares, post_video_views, post_video_views_organic)
    ) P

Table

Date       |post_id| metric_name               | metric_value| 
01/03/2020 |123    | post_impressions          | 100
01/03/2020 |123    | likes                     | 500
01/03/2020 |123    | comments                  | 700
01/03/2020 |123    | shares                    | 300
01/03/2020 |123    | post_video_views          | 3500
01/03/2020 |123    | posts_video_views_organic | 2800
01/05/2020 | 123   | post_impressions          | 500
01/05/2020 | 123   | shares                    | 750
01/07/2020 | 123   | comments                  | 550

Answer 1

Personally, I prefer conditional aggregation over the restrictive PIVOT operator. Assuming that the date doesn't matter, then it appears what you are after is the following:

WITH YourTable AS(
    SELECT *
    FROM (VALUES(CONVERT(date,'01/03/2020',103),123 ,'post_impressions         ',100),
                (CONVERT(date,'01/03/2020',103),123 ,'likes                    ',500),
                (CONVERT(date,'01/03/2020',103),123 ,'comments                 ',700),
                (CONVERT(date,'01/03/2020',103),123 ,'shares                   ',300),
                (CONVERT(date,'01/03/2020',103),123 ,'post_video_views         ',3500),
                (CONVERT(date,'01/03/2020',103),123 ,'posts_video_views_organic',2800),
                (CONVERT(date,'01/05/2020',103), 123,'post_impressions         ',500),
                (CONVERT(date,'01/05/2020',103), 123,'shares                   ',750),
                (CONVERT(date,'01/07/2020',103), 123,'comments                 ',550))V(Date,post_id,metric_name,metric_value))
SELECT post_id,
       MAX(CASE metric_name WHEN 'post_impressions' THEN metric_value END) AS post_impressions,
       MAX(CASE metric_name WHEN 'likes' THEN metric_value END) AS likes,
       MAX(CASE metric_name WHEN 'comments' THEN metric_value END) AS comments,
       MAX(CASE metric_name WHEN 'shares' THEN metric_value END) AS shares,
       MAX(CASE metric_name WHEN 'post_video_views' THEN metric_value END) AS post_video_views,
       MAX(CASE metric_name WHEN 'posts_video_views_organic' THEN metric_value END) AS posts_video_views_organic,
       MAX(CASE metric_name WHEN 'post_impressions' THEN metric_value END) AS post_impressions,
       MAX(CASE metric_name WHEN 'shares' THEN metric_value END) AS shares,
       MAX(CASE metric_name WHEN 'comments' THEN metric_value END) AS comments
FROM YourTable
GROUP BY post_id;