Header and Detail Data

Question

Header and Detail Data

bpowers

SSCertifiable

Points: 5483
More actions
January 4, 2016 at 4:49 pm

#307423

We have a Header table and a Detail Line table, which join on the Primary Key . However, we are trying to return the results set in the following manner:
Header
Detail
Header
Detail
Detail
Etc...
Each distinct Header record can have numerous Detail records. Union is not an option, as the number of columns returned from each are different. Any ideas on how to accomplish this with T-SQL? Any feedback will be greatly appreciated.
Thanks

Viewing 15 posts - 1 through 15 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

The columns in a single result set cannot vary between rows.

But maybe you can create a simple result set containing something like (RecId Rownum Data) where the Data column is varchar (max) and contains already delimited strings of all data required.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

bpowers (1/4/2016)
We have a Header table and a Detail Line table, which join on the Primary Key . However, we are trying to return the results set in the following manner:
Header
Detail
Header
Detail
Detail
Etc...
Each distinct Header record can have numerous Detail records. Union is not an option, as the number of columns returned from each are different. Any ideas on how to accomplish this with T-SQL? Any feedback will be greatly appreciated.
Thanks

What are you going to do with those results? I ask because it may make a difference in a possible suggestion I have. For example, are the results being returned to a GUI or are they ultimately destined for a file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Can you simply return two resultsets and then join those resultsets in your application for display? For example:

Resultset 1: 0..n header rows

Resultset 2: 0..n details rows where the ID relates to one of the header rows

If this style of returning results is application-imposed then will it matter if the number of resultsets change from one database request to the next? For example let's say you have a stored procedure that returns this data, if you call it with one set of inputs and it returns:

Resultset 1: Header Row A

Resultset 2: Detail Rows for A

Resultset 3: Header Row B

Resultset 4: Detail Rows for B

and then you call it again with a different set of inputs and it returns only two resultsets:

Resultset 1: Header Row C

Resultset 2: Detail Rows for C

Will your application be OK with that type of behavior, i.e. can it properly process the results when the number of resultsets is unknown ahead of time?

Will your application be OK if no resultsets are returned?

One option (maybe unpopular in some circles or unusable your given application) is to return an XML document which can support this type of hierarchical, zero-to-many style of object.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bpowers SSCertifiable Points: 5483 More actions · Answer 4

Thanks for all the feedback. The goal is to export the result set to a fixed length flat file and submit it to a Deltek CostPoint system for import, outside of our business. There is no front-end application on our side to put the two files together for processing, so we are trying to figure out how to get it put together in one text file for submitting via T-SQL.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

OK, so order is a requirement. There are several ways you can do it but since you have the data and the ETL tool to consider one data-oppption may pair with the ETL better than another. What toolset are you thinking of using to write the file?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bpowers SSCertifiable Points: 5483 More actions · Answer 6

We received verification that the data can be submitted with all the Header data then the Detail data, as such:

Header 06721

Header 05482

Header 04263

Detail 06721

Detail 05482

Detail 04263

Etc...

Not sure if that makes it any easier for us. Ideally would like to bcp it to a text file using code, but SSIS may be the way to go. Just do not know enough about SSIS to get it working. I see there is a For Loop option in SSIS that may work.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

I know SSIS can do either all header then all footer or all interleaved. I do not know if bcp can do it natively but am sure I could get there with bcp plus a call to the COPY to merge files together. I'll work something up later today, am mobile at the moment.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Phil Parkin SSC Guru Points: 246986 More actions · Answer 8

bpowers (1/5/2016)
We received verification that the data can be submitted with all the Header data then the Detail data, as such:
Header 06721
Header 05482
Header 04263
Detail 06721
Detail 05482
Detail 05482
Detail 04263
Etc...
Not sure if that makes it any easier for us. Ideally would like to bcp it to a text file using code, but SSIS may be the way to go. Just do not know enough about SSIS to get it working. I see there is a For Loop option in SSIS that may work.

SSIS can do this quite easily. You would need two dataflows, run sequentially, outputting to the same file (though referenced via two different connection managers, from memory).

Interleaved would be tough.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

bpowers SSCertifiable Points: 5483 More actions · Answer 9

Thanks for the feedback. Do you know of a good site that shows some details on creating something similar to this with SSIS? We do not have experience with SSIS, which is why we were looking into a bcp option. No time like the presence to learn though. It would be interesting to figure out how to do it both ways to see which performs better and which is easier to implement for production. Any further guidance will be greatly appreciated.

bpowers SSCertifiable Points: 5483 More actions · Answer 10

Ok I have two separate data flow tasks setup and can manually fire them off to populate the flat file. How do I set this up to run the Header then the Detail tasks automatically? Do I need to join the data flow tasks on the control flow page? Also, once it completes can SSIS be setup to email the file out? Any additional feedback will be greatly appreciated. This has actually been fun!

Phil Parkin SSC Guru Points: 246986 More actions · Answer 11

bpowers (1/5/2016)
Ok I have two separate data flow tasks setup and can manually fire them off to populate the flat file. How do I set this up to run the Header then the Detail tasks automatically? Do I need to join the data flow tasks on the control flow page? Also, once it completes can SSIS be setup to email the file out? Any additional feedback will be greatly appreciated. This has actually been fun!

Yes, create a 'precedence constraint' from the header task to the detail data flow task.

E-mail - yes. Join the 'detail' DFT to a (configured) Send Mail task.

Or, if you prefer, create a stored proc which sends e-mail and execute that via an Execute SQL task.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

@bpowers, sounds like you already got something going in SSIS. The interleaving requirement is much more interesting but if the data recipient will take all header data followed by all detail data in the file then you have a path to working solution and you got your feet wet with SSIS, very nice. If interleaving shows up again as a requirement post back to this thread and we can keep going.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 13

bpowers (1/5/2016)
We received verification that the data can be submitted with all the Header data then the Detail data, as such:
Header 06721
Header 05482
Header 04263
Detail 06721
Detail 05482
Detail 05482
Detail 04263
Etc...
Not sure if that makes it any easier for us. Ideally would like to bcp it to a text file using code, but SSIS may be the way to go. Just do not know enough about SSIS to get it working. I see there is a For Loop option in SSIS that may work.

I don't know much about SSIS so cannot make any judgment there. At the risk of causing severely elevated blood pressure for at least one of my good friends on this thread, either the "All Headers First" or the "Interleaved Headers" options are a piece of cake using BCP and another tool, which you may not be allowed to use, xp_CmdShell. And, no... we don't actually need SSIS if we can go that route. Lemme know if that's possible or not. I don't want to waste your time or mine if it's not an option.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bpowers SSCertifiable Points: 5483 More actions · Answer 14

Well after submitting the SSIS file for testing I was notified that the Header and Detail must be grouped together. Ughhh... If you still have a bcp option you'd be willing to work with me I would greatly appreciate it. Also, xp_cmdshell can be used as I will simply turn it on and off during the execution.

Thanks