MySQL How to place same records into a separate columns?

Viewed 49

Fiddle: http://sqlfiddle.com/#!9/4d4288/6

DDL and DML:

CREATE TABLE User(
  id INT,
  Name VARCHAR(50)
);

INSERT INTO User (id, Name)
VALUES
(1, 'Name 1'),
(2, 'Name 2'),
(3, 'Name 3');

CREATE TABLE Log(
  Id INT,
  Description VARCHAR(255));
  
INSERT INTO Log (id, Description)
VALUES
(1, 'This is issue 1 ...'),
(2, 'This is issue 2 ...'),
(3, 'This is issue 3 ...'),
(4, 'This is issue 3 ...');

CREATE TABLE LogActions(
  id INT,
  LogID INT,
  UserID INT,
  Status ENUM('Approved', 'Refused'));

INSERT INTO LogActions (id, LogID, UserID, Status)
VALUES
(1,1,1,'Approved'),
(2,1,2,'Approved'),
(3,2,1,'Approved'),
(4,2,3,'Approved');

Output

SELECT
  l.id,
  l.description,
  u.name,
  la.status
FROM Log l
LEFT JOIN 
  LogActions la ON l.id = la.LogID
LEFT JOIN
  User u ON u.id = la.UserID

---------------------------------------------
id  description             name    status
1   This is issue 1 ...     Name 1  Approved
1   This is issue 1 ...     Name 2  Approved
2   This is issue 2 ...     Name 1  Approved
2   This is issue 2 ...     Name 3  Approved
3   This is issue 3 ...     (null)  (null)
4   This is issue 4 ...     (null)  (null)

Summary

Here I can see which user marked specific log number and also which records havent.

Within the .NET application, I ensure the records are marked by 2 different users and no more than 2 records are checked off.

Goal

This is my desired output.

id  description             name1      name2    status1  status2  
1   This is issue 1 ...     Name 1     Name 2   Approved Approved
2   This is issue 2 ...     Name 1     Name 3   Approved Approved
3   This is issue 3 ...     (null)     (null)   (null)   (null)
4   This is issue 4 ...     (null)     (null)   (null)   (null)

This is what I aim to achieve. Essentially, I am needing to see 1 of each log number id, and on the same row show the users name and status that have/haven't marked this record.

This is the best solution I have at the moment, I'll be happy to hear for any alternatives.

Purpose

The main purpose of this is to have each log number in 1 row instead of multiple and visually inspect who has marked it or whether it is blank.

1 Answers

With FIRST_VALUE() window function:

SELECT DISTINCT
  l.id,
  l.description,
  FIRST_VALUE(u.name) OVER (PARTITION BY l.id ORDER BY la.id) name1,
  FIRST_VALUE(u.name) OVER (PARTITION BY l.id ORDER BY la.id DESC) name2,
  FIRST_VALUE(la.status) OVER (PARTITION BY l.id ORDER BY la.id) status1,
  FIRST_VALUE(la.status) OVER (PARTITION BY l.id ORDER BY la.id DESC) status2
FROM Log l
LEFT JOIN LogActions la ON l.id = la.LogID
LEFT JOIN User u ON u.id = la.UserID

See the demo.
Results:

> id | description         | name1  | name2  | status1  | status2 
> -: | :------------------ | :----- | :----- | :------- | :-------
>  1 | This is issue 1 ... | Name 1 | Name 2 | Approved | Approved
>  2 | This is issue 2 ... | Name 1 | Name 3 | Approved | Approved
>  3 | This is issue 3 ... | null   | null   | null     | null    
>  4 | This is issue 4 ... | null   | null   | null     | null    
Related