0

I have an MySQL input table like below. The Primary Key consists of PID and MID.

PID | MID | VAL
---------------
 1  |  1  | 50
 1  |  2  | 51
 1  |  3  | 52
 1  |  4  | 53
 2  |  1  | 25
 2  |  2  | 26
 3  |  1  | 11
 3  |  1  | 12
 3  |  2  | 13

And I need this format below, where you can see that the fixed number of 50 MID's are in the columns and all available PID's are in the rows. PID should be the Primary Key:

PID |  MID1  |  MID2  |  MID3  |  MID4  |  MID5  | .... | MID50  
---------------------------------------------------------------
 1  |   50   |   51   |   52   |   53   |    ..(null)..
 2  |   25   |   26   |      ..(null)..
 3  |   12   |   13   |      ..(null)..

Regarding SSIS: According to MSDN Pivot Article the PIVOT transformation "rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output" so I think this is not applicable in my case since MID is not unique.

In SQL / MySQL I'm not an expert so I hope you can help me. I've seen the MySQL Pivot query here which in my opinion goes to the right direction but cannot adapt this solution to my problem.

The solution might be a MySQL-Query or a SSIS transformation.

Community
  • 1
  • 1
17nxo
  • 87
  • 1
  • 3
  • 10

1 Answers1

0

something on these lines

SELECT
    pid,
    MAX(CASE mid WHEN 1 THEN val ELSE NULL END) as MID1,
    MAX(CASE mid WHEN 2 THEN val ELSE NULL END) as MID2,
    MAX(CASE mid WHEN 3 THEN val ELSE NULL END) as MID3,
    MAX(CASE mid WHEN 4 THEN val ELSE NULL END) as MID4,
    MAX(CASE mid WHEN 5 THEN val ELSE NULL END) as MID5,
    MAX(CASE mid WHEN 6 THEN val ELSE NULL END) as MID6
FROM inputTable
GROUP BY pid;

DEMO

John Ruddell
  • 25,283
  • 6
  • 57
  • 86