0

This question already has an answer here:

I have a table that has columns like following. Batch number is a database design thing like row number

dm_id        type_in_id    type_student_id     amount
 36              1               1               1
 36              1               2               2
 36              1               3               3
 36              2               1               4
 36              2               2               5
 36              2               3               6
 36              3               1               7
 36              3               2               8
 36              3               3               9

I want to create a query that would return this:

dm_id  amount1 amount2 amount3 amount4 amount5 amount6 amount7 amount8 amount9
 36       1       2       3       4       5       6       7       8       9

How can I do that?

P.S. Version 5.5.56-MariaDB - MariaDB Server

marked as duplicate by hot2use, Colin 't Hart, mustaccio, RDFozz, Md Haidar Ali Khan Sep 26 '18 at 3:47

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Please tag your RDBMS, version included. – McNets Sep 25 '18 at 7:13
  • 1
    It is referred to as "pivot". Its realization is strongly DBMS-dependent - so specify it, including version. – Akina Sep 25 '18 at 7:15
  • Version 5.5.56-MariaDB - MariaDB Server – Attapol Teachabutrasri Sep 25 '18 at 7:54
  • Stored procedure with dynamic SQL only... or (if dm_id-amount pairs are unique and amount values list is fixed) "pivot via grouping and aggregate". – Akina Sep 25 '18 at 8:48
  • 1
    type_in_id and type_student_id fields values are ignored? amount values are used as both column headers and pivot values? strange... – Akina Sep 25 '18 at 8:55
0
SELECT dm_ID, MAX(Amount1) Amount1, MAX(Amount2) Amount2,...
FROM (
SELECT dm_id,
CASE Value WHEN 1 THEN Value ELSE NULL END AS Amount1,
CASE Value WHEN 2 THEN Value ELSE NULL END AS Amount2,
....
FROM Table
) RearrangedData

Old school pivot. It will work, but you need to know the possible values of Amount column, to use in the CASE statement... TSQL so should be supported by mySQL.

Not the answer you're looking for? Browse other questions tagged or ask your own question.