CODEWITHSUNDEEP
mysqlsql
J86
J86

Reputation: 15307

Pivoting query giving nulls for other required columns

I have the following table:

 create table table1 (
      Id int not null auto_increment,
      JobId varchar(255) not null,
      FieldOrder int(11) not null,
      FieldName varchar(255) not null,
      FieldValue varchar(255) not null,
      primary key (Id)
    );

It has the following data:

| Id  | JobId | FieldOrder | FieldName    | FieldValue |
| --- | ----- | ---------- | ------------ | ---------- |
| 1   | 1     | 1          | Customer Id  | C01        |
| 2   | 1     | 2          | Order Number | 3923       |
| 3   | 1     | 3          | Architect Id | DK         |
| 4   | 2     | 1          | Customer Id  | C02        |
| 5   | 2     | 2          | Order Number | 23         |
| 6   | 2     | 3          | Architect Id | AJ         |
| 7   | 3     | 1          | Customer Id  | C03        |
| 8   | 3     | 2          | Plot Id      | 3          |
| 9   | 3     | 3          | Architect Id |            |

I am trying to write a pivoting query so that I can get the following:

+-------------+--------------+--------------+
| customer_id | order_number | architect_id |
+-------------+--------------+--------------+
| C01         | 3923         | DK           |
| C02         | 23           | AJ           |
| C03         |              |              |
+-------------+--------------+--------------+

CURRENT PROGRESS

So far my query looks like this:

select case
           when FieldName = 'Customer Id' then FieldValue
       end as "customer_id",
      case
          when FieldName = 'Order Number' then FieldValue
      end as "order_number",
      case
          when FieldName = 'Architect Id' then FieldValue
      end as "architect_id"
from table1
group by JobId;

Locally, this gives me the following:

+-------------+--------------+--------------+
| customer_id | order_number | architect_id |
+-------------+--------------+--------------+
| C01         | NULL         | NULL         |
| C02         | NULL         | NULL         |
| C03         | NULL         | NULL         |
+-------------+--------------+--------------+

I suspect because when I do group by I no longer has access to my fields, so I'm stuck, I'm not really sure how I can get it to work.

I have created a db-fiddle too, though it won't even run due to some defaulted setting.

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

You want aggregation:

select jobid,
       max(case when FieldName = 'Customer Id' then FieldValue
           end) as customer_id,
       max(case when FieldName = 'Order Number' then FieldValue
           end) as order_number,
       max(case when FieldName = 'Architect Id' then FieldValue
           end) as architect_id
from table1
group by JobId;

Notes:

  • I added jobid to the select. Normally, you would want to distinguish the rows.
  • Don't escape identifiers that don't need to be escaped. I removed the double quotes. They are not needed.

Upvotes: 2

Related Questions