How to get the output of a SQLplus query in a single column?-CodePudding

I have a sql query which has multiple columns and records. I want the records to be in a single column in the output file. How can I achieve this ?

Sample code

sqlplus -s user/password@db < mysql.sql > output.txt

output

Auto Insurance ASTXXXRT 0000123
Reds PI        GBT12XX1 0023871
               BRSXXTW2 
Agent DZ                0238287

expected output

Auto Insurance
ASTXXXRT
0000123
Reds PI
GBT12XX1
0023871
BRSXXTW2
Agent DZ
0238287

cat mysql.sql 
select a."Name",b."Code",b."Acc" from "tab_a" a join "tab_b" b on a."id"=b."id" join "tab_c" c on b."id"=c."id"

I want all the records to be stored in a single column in the output file so that I can perform few checks later on in the code.

CodePudding user response：

While I think solving this in SQL is not a great route, you'll have to use a UNION here to pull it off:

SELECT outputcol 
FROM 
   (
     select a."Name" as sortcol, 1 as sortcol2, a."Name" as outputcol from "tab_a" a join "tab_b" b on a."id"=b."id" join "tab_c" c on b."id"=c."id"
     UNION ALL
     select a."Name", 2, b."Code" from "tab_a" a join "tab_b" b on a."id"=b."id" join "tab_c" c on b."id"=c."id"
     UNION ALL
     select a."Name",3, b."Acc" from "tab_a" a join "tab_b" b on a."id"=b."id" join "tab_c" c on b."id"=c."id"
   )sub
ORDER BY sortcol, sortcol2;

Note that this will fail if a."name" is non-unique. If that's the case you'll have to identify another column in these joined tables to use as your sort key.

A better way would be to write output that can be parsed by awk (using a different delimter besides a space character would help. Then you could just run it through something like awk -F"|" '{for (i=1;i<=NF;i ){print $i}}' output.txt and be done with it.

CodePudding user response：

If you don't care about the order then this should work:

select * from tab_a
union all
select * from tab_b
union all
select * from tab_c

if you do care about the order let me know I can adjust it.