CODEWITHSUNDEEP
mysqlsqloracle-database
gopak86
gopak86

Reputation: 33

SQL merge/transform row information into columns

Database

I have a database as shown in the picture. Let's use following data as example:

WORKORDER table:

WONUM (PK)  |  WORKTYPE  | STATUS |  DESCRIPTION
1234           URGENT      OPEN      Inv. work X.

FAILUREREPORT table:

FAILUREREPORTID (PK) | WONUM | FAILURECODE | ASSETNUM  | TYPE   
5678                   1234    SYMPT1347     DT-JA-123   Problem
5679                   1234    WA4521        DT-JA-123   Cause
5680                   1234    SOL1672       DT-JA-123   Solution

FAILURECODE table:

FAILURECODEID (PK) | FAILURECODE | DESCRIPTION
345                  SYMPT1347     Symptom X.
346                  WA4521        Cause Y.
347                  SOL1672       Solution Z.

I have the following query, which does the job half-way:

SELECT F.WONUM, W.DESCRIPTION AS WO_DESC, W.STATUS, F.ASSETNUM, F.TYPE, C.DESCRIPTION AS FCODE_DESC
FROM WORKORDER W
INNER JOIN FAILUREREPORT F ON F.WONUM = W.WONUM
INNER JOIN FAILURECODE C ON F.FAILURECODE = C.FAILURECODE
WHERE W.WORKTYPE = 'URGENT' AND W.STATUS <> 'CANCELLED'
ORDER BY F.WONUM ASC, F.TYPE ASC

It returns:

WONUM | WO_DESC              | STATUS | ASSETNUM | TYPE    | FCODE_DESC
1234    Inv. work X.           OPEN     DT-JA-123  PROBLEM   Symptom X.
1234    Inv. work X.           OPEN     DT-JA-123  CAUSE     Cause Y.
1234    Inv. work X.           OPEN     DT-JA-123  SOLUTION  Solution Z.

However, instead of 3 rows, I would like to have the symptom, cause and solution listed in one row, like this:

WONUM | WO_DESC     | STATUS | ASSETNUM | PROBLEM   | CAUSE  |  SOLUTION
1234    Inv. work X.  OPEN     DT-JA-123  Symptom X.  Cause Y.  Solution Z.

How do I use pivot function to transform the rows into columns, if it is possible? I am also open to any other solution that leads to the desired result. The database I operate in is Oracle DB, while my playground/test environment is MySQL. I am aware that pivot cannot be used in MySQL, but there are ways around it by aggregation and grouping.

Upvotes: 2

Views: 63

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You need a case statement. Try this

SELECT F.WONUM, 
       W.DESCRIPTION AS WO_DESC, 
       W.STATUS, 
       F.ASSETNUM, 
       MIN (CASE WHEN TYPE = 'PROBLEM' THEN C.DESCRIPTION END) AS PROBLEM,
       MIN (CASE WHEN TYPE = 'CAUSE' THEN C.DESCRIPTION END) AS CAUSE,
       MIN (CASE WHEN TYPE = 'SOLUTION' THEN C.DESCRIPTION END) AS SOLUTION
FROM WORKORDER W
INNER JOIN FAILUREREPORT F ON F.WONUM = W.WONUM
INNER JOIN FAILURECODE C ON F.FAILURECODE = C.FAILURECODE
WHERE W.WORKTYPE = 'URGENT' 
AND W.STATUS <> 'CANCELLED'
GROUP BY F.WONUM, 
         W.DESCRIPTION, 
         W.STATUS, 
         F.ASSETNUM
ORDER BY F.WONUM ASC

Upvotes: 2

markusk
markusk

Reputation: 6677

Use subqueries:

SELECT w.wonum, w.description AS wo_desc, w.status,
  (SELECT assetnum FROM FAILUREREPORT WHERE wonum = w.wonum LIMIT 1) assetnum,
  (SELECT description
   FROM FAILUREREPORT JOIN FAILURECODE USING(failurecode)
   WHERE type = 'PROBLEM' AND wonum = w.wonum) problem,       
  (SELECT description
   FROM FAILUREREPORT JOIN FAILURECODE USING(failurecode)
   WHERE type = 'CAUSE' AND wonum = w.wonum) cause,       
  (SELECT description
   FROM FAILUREREPORT JOIN FAILURECODE USING(failurecode)
   WHERE type = 'SOLUTION' AND wonum = w.wonum) solution
FROM WORKORDER w
WHERE w.worktype = 'URGENT' AND w.status <> 'CANCELLED'
ORDER BY wonum ASC

Upvotes: 1

Related Questions