CODEWITHSUNDEEP
sqlbashcsv
Anarko_Bizounours
Anarko_Bizounours

Reputation: 501

How to convert the result of a sql query to a csv format

My problem is quite simple, but I'm stuck on it for 2 weeks and not able to step back and see the possible solution.

I have a result of a sql query looking like that :

USR  Count  DATE
u1       9  160920
U1       2  160918
U1       5  160922
U2      19  160924
U3       2  160919
U3       1  160921
U4      12  160921

And I want to convert this result in a csv format to look like that using BASH due to limitation on the server at work :

  ;160918;160919;160920;160921;160922;160923;160924
U1;     2;     0;     9;     0;     5;     0;     0
U2;     0;     0;     0;     0;     0;     0;    19
U3;     0;     2;     0;     1;     0;     0;     0
U4;     0;     0;     0;    12;     0;     0;     0

I'm stuck, I succeed in converting the line as a csv format, but when I try to regroup duplicate user in one line, I fail miserably.

Do you have any tips or idea ?

Upvotes: 2

Views: 162

Answers (1)

Teja
Teja

Reputation: 13544

SELECT Z.Col1 AS " ",
       MAX( CASE WHEN Z.Col3 = 160918 THEN Z.Col2 ELSE 0 END )   "160918", 
       MAX( CASE WHEN Z.Col3 = 160919 THEN Z.Col2 ELSE 0 END )   "160919",
       MAX( CASE WHEN Z.Col3 = 160920 THEN Z.Col2 ELSE 0 END )   "160920",
       MAX( CASE WHEN Z.Col3 = 160921 THEN Z.Col2 ELSE 0 END )   "160921",
       MAX( CASE WHEN Z.Col3 = 160922 THEN Z.Col2 ELSE 0 END )   "160922",
       MAX( CASE WHEN Z.Col3 = 160923 THEN Z.Col2 ELSE 0 END )   "160923",
       MAX( CASE WHEN Z.Col3 = 160924 THEN Z.Col2 ELSE 0 END )   "160924"
   FROM
        (
        SELECT *
          FROM
        ( SELECT 'u1' AS Col1, 9  AS  Col2,  160920 AS Col3) UNION ALL
        ( SELECT 'u1' AS Col1, 2  AS  Col2,  160918 AS Col3) UNION ALL
        ( SELECT 'u2' AS Col1, 19 AS  Col2,  160924 AS Col3) UNION ALL
        ( SELECT 'u3' AS Col1, 2  AS  Col2,  160919 AS Col3) UNION ALL
        ( SELECT 'u4' AS Col1, 1  AS  Col2,  160921 AS Col3) UNION ALL
        ( SELECT 'u4' AS Col1, 12 AS  Col2,  160921 AS Col3)
        ) Z
  GROUP BY Z.Col1
  ORDER BY Z.Col1
;

Upvotes: 2

Related Questions