Pivot table based on pairs

Question

I have a table that has the columns ID, A, A_potential, B, B_potential, ... Some columns have value, some do not. If A doesn't have value, A_potential doesn't either. I want to only display the columns that have value but also pair the columns with each other. Then loop it so it displays them paired together if they have value.

WHAT I WANT TO SHOW

<div class="item">
  <div class="current">30</div> <!--A Value-->
  <div class="potential">35</div> <!--A Potential-->
</div>

<div class="item">
  <div class="current">50</div> <!--C Value-->
  <div class="potential">75</div> <!--C Potential-->
</div>

CURRENT TABLE

ID | A  | A_potential | B  | B_potential
--------------------------------------
1  | 35 | 50          | 15 | 30
2  | 0  | 0           | 10 | 70

WHAT I WANT TABLE TO LOOK LIKE FOR LOOP PURPOSES

ID | Type | Current | Potential
--------------------------
 3 | A    |  35     | 50
 3 | D    |  50     | 75

I do not get the logic that would get you from the current table to the expected output. — Shadow, Nov 30 '17 at 21:56

score 1 · Accepted Answer · answered Nov 30 '17 at 22:08

Use UNION of queries for each pair.

SELECT id, 'A' AS Type, A AS Current, A_Potential AS Potential
FROM yourTable
WHERE A != 0 OR A_Potential != 0
UNION ALL
SELECT id, 'B' AS Type, B AS Current, B_Potential AS Potential
FROM yourTable
WHERE B != 0 OR B_Potential != 0
UNION ALL
SELECT id, 'C' AS Type, C AS Current, C_Potential AS Potential
FROM yourTable
WHERE C != 0 OR C_Potential != 0
...

Pivot table based on pairs

1 Answers1