A question from Experts Exchange asks how they can extract data from a database into a Fixed Width CSV file, but the number of columns (Employments) of the Persons in the rows are unknown at design time. Also, the file generation needs to be automated.
The question can be found at Solved: SQL Server flattening table | Experts Exchange
Most of the answers sofar tries to solve the solution using a single script, which makes it rather more complex that it should be and will put strain on the Server.
To make it simpler, we can answer the question, using Linx 5, and break the solution up into 3 specific components.
Firstly, you need to create the header for the CSV file, at which point you’ll need to know how many columns are necessary. To do that a simple count query can be done in the ExecuteSQL component:
select Count(1) from Employement where PersonID =(
SELECT Top 1 PersonID
FROM Employement
GROUP BY PersonID
ORDER BY count(*) desc)
Now that you know how many columns you need, you can populate the header into the file using the TextFileWrite component.
Secondly you need to get each person, using the ExecuteSQL component again, and add their info to a text line:
select PersonID, PersonName from Person
Then lastly, you can get all the Employments of the specific person and append them to the text line as well.
select EmployerName, Title
from Employement where PersonID = @{GetEachPerson.ForEachRow.PersonID}
To format the spacing of the text, use the “PadRight()” on each string.
Your solution will look like this:
Attached is also the solution: Annie1.lsoz (8.7 KB)
Lastly, to automate the generation of the file, you can simply create a Timer Service and add the process to the Timer’s event.
Example of an extracted file:
Person ID PersonName EmployerName1 Title1 EmployerName2 Title2 EmployerName3 Title3 EmployerName4 Title4
301 James CITI Teller Barclays Teller Bank of America Teller BoE Clerk
302 Ed PIH Hospital Sr. Nurse Kaiser Nurse
303 Peter Bank of America IT Support