【Question】

I have a table like this:
Month   ID   Category   Company   Dept   EntryDate   Team
201305   1009   1   A    A1    201108   R&D
201305   1009   1   B    B1    201207   Marketing
201305   1009   1   C    C1    201301   Support
201305   1009   1   D    D1    201109   Service
201305   1013   2   C    C2    201302   Support
201305   1027   2   A    A3    201007   R&D
...   ...   ...   ...   ...   ...   ...

The table has 4 million records. I want to export the file as multiple txt files by Team (Desired result is shown below). But I only find solutions of exporting a file as multiple Excel files. Is there any suggestion?
R&D.txt:
Month   ID   Category   Company   Dept   EntryDate   Team
201305   1009   1   A   A1   201108   R&D
201305   1027   2   A   A3   201007   R&D
Marketing.txt:
Month   ID   Category   Company   Dept   EntryDate   Team
201305   1009   1   B   B1   201207   Marketing
Support.txt:
Month   ID   Category   Company   Dept   EntryDate   Team
201305   1009   1   C   C1   201301   Support
201305   1013   2   C   C2   201302   Support
Service.txt:
Month   ID   Category   Company   Dept   EntryDate   Team
201305   100   1   D   D1   201109   Service

【Answer】

A natural solution is grouping records by Team and then exporting data in each group to a txt file named after the corresponding team. Yet there is a problem. SQL can’t retain the detail data of a group because it rules that an aggregation must be bundled to a grouping operation. So it’s really a hassle to do it in the language. In your case, there is a huge amount of data which needs to be batch exported to a txt file with the cursor. This makes the process even more complicated.

Here’s a better way of handling this. SPL provides For loop statement to handle such scenarios. Below is the SPL script:

A1: Retrieve data in SQL and return it as a database cursor;

A2-B4: Loop over A1’s cursor; retrieve 10000 records each time, group them by Team and append each group to the corresponding txt file.