SQL Server grouping data-CodePudding

One of our log table got result like below

Unique__Nm  reviewer 1    reviewer 2    reviewer 3
--------------------------------------------------
  859          NULL          NULL        Joel
  859          NULL         Joseph       NULL
  859         Antony         NULL        NULL
  163         Robert         NULL        NULL
  163          NULL         Joseph       NULL
  164          NULL         Andrew       NULL
  896          NULL          karry       NULL

I need to group data based on Unique number and extract result like below

Unique__Num  reviewer 1   reviewer 2    reviewer 3
------------------------------------------------
859            Antony     Joseph        Joel
163            Robert     Joseph        NULL
164            NULL       Andrew        NULL
896            NULL       Kerry         NULL

Can someone help?

CodePudding user response：

What if your input is like:

Unique__Nm  reviewer 1    reviewer 2    reviewer 3
--------------------------------------------------
  859          NULL          NULL        Joel
  859          NULL         Joseph       NULL
  859         Antony         NULL        NULL
  859          NULL         Edmund       NULL

What should be reviewer 2 in the output?

If such input is possible at all, you should specify what should happen, ie. what should we get as rewiever 2.

Otherwise this will do the work:

SELECT Unique__Nm, max([reviewer 1]), max([reviewer 2]), max([reviewer 3])
  FROM table
 GROUP BY Unique__Nm;

CodePudding user response：

If all of reviewer 1, reviewer 2 and reviewer 3 columns can have only one not null value per Unique__Nm, you can use simple coalesce per column grouped by Unique_Nm values, e.g.

SELECT 
    Unique__Nm, 
    COALESCE([reviewer 1]) as [reviewer 1], 
    COALESCE([reviewer 2]) as [reviewer 2], 
    COALESCE([reviewer 3]) as [reviewer 3] 
FROM table 
GROUP BY Unique__Nm

CodePudding user response：

Your data

CREATE TABLE test(
   Unique_Nm INTEGER  NOT NULL
  ,[reviewer 1] VARCHAR(60)
  ,[reviewer 2] VARCHAR(60)
  ,[reviewer 3] VARCHAR(60)
);
INSERT INTO test
(Unique_Nm,[reviewer 1],[reviewer 2],[reviewer 3]) VALUES 
(859,NULL,NULL,'Joel'),
(859,NULL,'Joseph',NULL),
(859,'Antony',NULL,NULL),
(163,'Robert',NULL,NULL),
(163,NULL,'Joseph',NULL),
(164,NULL,'Andrew',NULL),
(896,NULL,'karry',NULL);

using provide String_agg provide better situation where exist two [reviwer] per Unique_Nm and it will concat them, In addition, it mask null values.

select Unique_Nm,
string_agg([reviewer 1],',') [reviewer 1],
string_agg([reviewer 2],',') [reviewer 2],
string_agg([reviewer 3],',') [reviewer 3]
from test
group by Unique_Nm

however using Max is also possible

select Unique_Nm,
MAX([reviewer 1]) [reviewer 1],
MAX([reviewer 2]) [reviewer 2],
MAX([reviewer 3]) [reviewer 3]
from test
group by Unique_Nm

dbfiddle