CODEWITHSUNDEEP
jquerymysqlsqloracle-database
Tester
Tester

Reputation: 808

How to write this query to get proper result?

I have the following table

+----+----------+------+
|D_ID|date      |value |
+----+----------+------+
|0   |1992-02-01|119940|
+----+----------+------+
|1   |1992-02-01|119941|
+----+----------+------+
|1   |1992-02-02|119942|
+----+----------+------+
|2   |1992-02-01|119943|
+----+----------+------+
|0   |1992-02-02|119944|
+----+----------+------+
|0   |1992-02-03|119945|
+----+----------+------+
|2   |1992-02-02|119946|
+----+----------+------+
|1   |1992-02-04|119947|
+----+----------+------+
|1   |1992-02-05|119948|
+----+----------+------+
|2   |1992-02-02|119949|
+----+----------+------+
|2   |1992-02-03|119940|
+----+----------+------+
|0   |1992-02-04|119940|
+----+----------+------+

Is there any way to get the following result? I want to get everyday's D_ID value.

+----------+------+------+------+
|date      |D_ID_0|D_ID_1|D_ID_2|
+----------+------+------+------+
|1992-02-01|119940|119941|119943|
+----------+------+------+------+
|1992-02-02|119944|119942|119949|
+----------+------+------+------+
|1992-02-03|119945|      |119940|
+----------+------+------+------+
|1992-02-04|119940|119947|      |
+----------+------+------+------+
|1992-02-05|      |119948|      |
+----------+------+------+------+

Upvotes: 2

Views: 91

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Hope this helps!

ORACLE:

SELECT date ,
       MAX(DECODE(D_ID,0,value,NULL)) as D_ID_0,
       MAX(DECODE(D_ID,1,value,NULL)) as D_ID_1,
       MAX(DECODE(D_ID,2,value,NULL)) as D_ID_2
FROM 
     your_table
GROUP BY date ;

MySQL:

SELECT date ,
       MAX(IF(D_ID=0,value,NULL)) as `D_ID_0`,
       MAX(IF(D_ID=1,value,NULL)) as `D_ID_1`,
       MAX(IF(D_ID=2,value,NULL)) as `D_ID_2`
FROM 
     your_table
GROUP BY date ;

OR

SELECT date ,
       MAX((CASE WHEN (d_id = 0) THEN value ELSE NULL end)) AS `D_ID_0`,
       MAX((CASE WHEN (d_id = 1) THEN value ELSE NULL end)) AS `D_ID_1`,
       MAX((CASE WHEN (d_id = 2) THEN value ELSE NULL end)) AS `D_ID_2`
FROM 
     your_table
GROUP BY date ;

Upvotes: 2

user3199319
user3199319

Reputation: 1

Store the original table cell values in a double array. Sort the values of the D_ID in an unique array. Use a for loop, or in jquery "each", to create the new table cells. Within the for loops, the D_ID should find matches. Concatenate the original header of column 1 ([n[[0]) with the unique row values and place as the of the new table. Store and sort the rows that match and place at .

Information on arrays in javascript

http://www.caveofprogramming.com/frontpage/articles/javascript-2/javascript-array-how-to-use-arrays-in-javascript/

Table Cells collection http://www.w3schools.com/jsref/coll_table_cells.asp

Upvotes: 0

San
San

Reputation: 4538

Use Pivot

WITH tab(D_ID,d_date,d_value) AS
(SELECT 0 , '1992-02-01', 119940 FROM dual UNION ALL
SELECT 1, '1992-02-01', 119941 FROM dual UNION ALL
SELECT 1, '1992-02-02', 119942 FROM dual UNION ALL
SELECT 2, '1992-02-01',  119943 FROM dual UNION ALL
SELECT 0, '1992-02-02', 119944 FROM dual UNION ALL
SELECT 0, '1992-02-03', 119945 FROM dual UNION ALL
SELECT 2, '1992-02-02', 119946 FROM dual UNION ALL
SELECT 1, '1992-02-04', 119947 FROM dual UNION ALL
SELECT 1, '1992-02-05', 119948 FROM dual UNION ALL
SELECT 2, '1992-02-02', 119949 FROM dual UNION ALL
SELECT 2, '1992-02-03', 119940 FROM dual UNION ALL
SELECT 0, '1992-02-04', 119940 FROM dual)
-------
--End of Data
-------
SELECT * FROM tab
pivot (min(d_VALUE) AS dd_value FOR d_id IN (0 ,1 ,2));

output:

|     D_DATE | 0_DD_VALUE | 1_DD_VALUE | 2_DD_VALUE |
|------------|------------|------------|------------|
| 1992-02-04 |     119940 |     119947 |     (null) |
| 1992-02-03 |     119945 |     (null) |     119940 |
| 1992-02-02 |     119944 |     119942 |     119946 |
| 1992-02-05 |     (null) |     119948 |     (null) |
| 1992-02-01 |     119940 |     119941 |     119943 |

Upvotes: 1

Related Questions