使用列名从db2导出数据 [英] Export data from db2 with column names

问题描述

我想将数据从db2表导出为csv格式。我还需要第一行应该是所有列名。

I want to export data from db2 tables to csv format.I also need that first row should be all the column names.

通过使用

I have little success by using the following comand

EXPORT TO "TEST.csv" 
OF DEL 
MODIFIED BY NOCHARDEL coldel: ,
SELECT col1,'COL1',x'0A',col2,'COL2',x'0A' 
FROM TEST_TABLE;

但是这样我就得到了

Row1 Value:COL1:
Row1 Value:COL2:
Row2 Value:COL1:
Row2 Value:COL2:

等。

我也尝试了以下查询

EXPORT TO "TEST.csv" 
OF DEL 
MODIFIED BY NOCHARDEL 
SELECT 'COL1',col1,'COL2',col2 
FROM ADMIN_EXPORT;

但这会列出使用excel打开时每一行数据的列名。

But this lists column name with each row data when opened with excel.

有没有一种方法可以获取以下格式的数据

Is there a way i can get data in the format below

COL1   COL2
value  value
value  value

谢谢

推荐答案

经过几天的搜索，我以这种方式解决了这个问题：

After days of searching I solved this problem that way:

 EXPORT TO ...
 SELECT 1 as id, 'COL1', 'COL2', 'COL3' FROM sysibm.sysdummy1
 UNION ALL
 (SELECT 2 as id, COL1, COL2, COL3 FROM myTable)
 ORDER BY id

您不能一无所获地选择db2中的常量字符串，因此必须从sysibm.sysdummy1中进行选择。
要在第一行中添加手动添加的列，您必须添加一个伪ID并按该ID对UNION结果进行排序。否则标题可以位于结果文件的底部。

You can't select a constant string in db2 from nothing, so you have to select from sysibm.sysdummy1. To have the manually added columns in first row you have to add a pseudo-id and sort the UNION result by that id. Otherwise the header can be at the bottom of the resulting file.

这篇关于使用列名从db2导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！