January 10, 2023 at 10:48 pm
I create a sql query to create a feed file, my issue is that the file have a header and detail. The studen ID is the header and the classes are the detail.
Select st.student_id, st.academics_year, st.academic_term, cl.class, cl.schedule, cl.days
from student_term st
left join on classes cl on cl.year = st.academic_year and cl.term=st.academics_term
My query results :
12345 mat01, 08:00 to 09:00 TW
12345 fin101, 10:00 to 11:00 MF
12345 bio101, 14:00 to 15:00 MF
What I need :
12345
mat01, 08:00 to 09:00 TW
fin101, 10:00 to 11:00 MF
bio101, 14:00 to 15:00 MF
January 11, 2023 at 9:08 am
Given that the results of a single query must always have the same number (and datatype) of columns, there's no straightforward way of doing this. With some juggling, you may be able to pump all of the data into a single column, but if you want someone to attempt that, please provide your sample data in a consumable format (with CREATE TABLE and INSERT statements).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 12, 2023 at 10:59 am
Two options as I see it (along the lines of what Phil mentioned):
Create a view that unions the header, body and footer rows as a single column output.
Create a dtsx packages that outputs the three rows to the same file (using the file options to create new with the footer and append to file with the body and footer rows).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply