CODEWITHSUNDEEP
sqloracle-databasepivot
Surendra Singh
Surendra Singh

Reputation: 19

SQL query: Concatination of columns in sql : Oracle

I have a table with the following data:

col-1   col-2   col-3   col-4   col-5   col-6   col-7
-----   -----   -----   -----   -----   -----   -----
10      2       30      13      A       0.1     0.3
10      2       30      13      B       0.2     0.9
10      2       30      13      C       0.8     0.7
10      2       30      13      D       0.4     0.5
2       1       8       11      A       0.3     0.4
2       1       8       11      B       0.9     0.2
2       1       8       11      C       0.1     0.8
2       1       8       11      D       0.2     0.5

I have to come up with the following table: (here col-1 to col-4 have same values for 4 different values of col-5)

col-1    col-2    col-3    col-4    A_6    B_6    C_6    D_6    A_7    B_7  
-----    -----    -----    -----    ---    ---    ---    ---    ---    ---  
10        2         30      13      0.1    0.2    0.8    0.4    0.3    0.9  
2         1         8       11      0.3    0.9    0.1    0.2    0.4    0.2

Any idea how to achieve this?

Consider A_6 as A_COL_6 means Col_6 Value for A

Upvotes: 0

Views: 97

Answers (2)

Hasan Alizada
Hasan Alizada

Reputation: 591

WITH
    temp_t
    AS
        (SELECT '10'     col_1,
                '2'      col_2,
                '30'     col_3,
                '13'     col_4,
                'A'      col_5,
                0.1      col_6,
                0.3      col_7
           FROM DUAL
         UNION ALL
         SELECT '10', '2', '30', '13', 'B', 0.2, 0.9 FROM DUAL
         UNION ALL
         SELECT '10', '2', '30', '13', 'C', 0.8, 0.7 FROM DUAL
         UNION ALL
         SELECT '10', '2', '30', '13', 'D', 0.4, 0.5 FROM DUAL
         UNION ALL
         SELECT '2', '1', '8', '11', 'A', 0.3, 0.4 FROM DUAL
         UNION ALL
         SELECT '2', '1', '8', '11', 'B', 0.9, 0.2 FROM DUAL
         UNION ALL
         SELECT '2', '1', '8', '11', 'C', 0.1, 0.8 FROM DUAL
         UNION ALL
         SELECT '2', '1', '8', '11', 'D', 0.2, 0.5 FROM DUAL)
SELECT *
  FROM temp_t
       PIVOT (SUM (col_6) AS c6, SUM (col_7) AS c7
             FOR (col_5)
             IN ('A', 'B', 'C', 'D'))

Upvotes: 3

D-Shih
D-Shih

Reputation: 46229

You can use CASE WHEN expression with Aggregate function

WITH T AS(
     SELECT '10'  col_1,'2'   col_2,'30'  col_3,'13' col_4,'A'  col_5, '0.1'  col_6,'0.3' col_7 FROM DUAL
     UNION ALL
     SELECT '10', '2', '30', '13', 'B', '0.2','0.9' FROM DUAL
     UNION ALL
     SELECT '10', '2', '30', '13', 'C', '0.8','0.7' FROM DUAL
     UNION ALL
     SELECT '10', '2', '30', '13', 'D', '0.4','.5' FROM DUAL
     UNION ALL
     SELECT '2' ,'1' ,'8' ,'11','A' , '0.3','0.4'   FROM DUAL
     UNION ALL
     SELECT '2', '1', '8', '11', 'B', '0.9','0.2'   FROM DUAL
     UNION ALL
     SELECT '2', '1', '8', '11', 'C', '0.1','0.8'   FROM DUAL
     UNION ALL
     SELECT '2', '1', '8', '11', 'D', '0.2','0.5'   FROM DUAL
 )
 SELECT COL_1,
        COL_2,
        COL_3,
        COL_4,
        MAX(CASE WHEN COL_5 = 'A' THEN COL_6 ELSE '0' END) A_6,
        MAX(CASE WHEN COL_5 = 'B' THEN COL_6 ELSE '0' END) B_6,
        MAX(CASE WHEN COL_5 = 'C' THEN COL_6 ELSE '0' END) C_6,
        MAX(CASE WHEN COL_5 = 'D' THEN COL_6 ELSE '0' END) D_6,
        MAX(CASE WHEN COL_5 = 'A' THEN COL_7 ELSE '0' END) A_7,
        MAX(CASE WHEN COL_5 = 'B' THEN COL_7 ELSE '0' END) B_7
 FROM T
 GROUP BY 
        COL_1,
        COL_2,
        COL_3,
        COL_4

SQLFIDDLE:http://sqlfiddle.com/#!4/b9bb1/21

Upvotes: 2

Related Questions