Repeat Header info in first row of a combined Header/Lines view or table

Question

Repeat Header info in first row of a combined Header/Lines view or table

jake greene

Grasshopper

Points: 12
More actions
April 21, 2006 at 8:16 pm

#365986

Hi All,
Having difficulty trying to figure this one out. I need to create a view or another table from a Header and its related Lines table. My problem is that, in the View/Table, I only want the header information repeated 1 time in the first row of the View/Table. For example:
Header(Orders)
So#   OrderAmt
123     500.00
456     1000.00
Lines(OrderDetails)
SO#   Item        Qty            Price       Extension
123     widget     1               250.00     250.00
123     gadget     1               250.00     250.00
456     Widget     2               250.00      500.00
456     gadget     2               250.00      500.00
My Desired result(view or table)
SO# Item        Qty              Price       Extension   OrderTotal
123   widget       1                250.00    250.00       500.00
123   gadget       1                250.00    250.00     <null>
456   widget        2               250.00     500.00       1000.00
456    gadget       2               250.00     500.00       <null>

Can this be done via TSQL?

Thanks,

Jake

Viewing 3 posts - 1 through 3 (of 3 total)

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

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Yes it can be done. It is maybe a bit questionable way of displaying the data, but you can do it.

I suppose your table for Order Lines has line number, or some similar column. Use this in the query, and use CASE to display OrderTotal only if line number is 1 (or, better, when line number is the smallest existing for this particular order).

SELECT hdr.SO, lin.item, lin.Qty, lin.price, lin.extension,

CASE WHEN lin.linenumber = (select MIN(linenumber) from OrderLine where SO = hdr.SO) THEN hdr.OrderAmt ELSE NULL END AS OrderTotal

FROM OrderHeader hdr

JOIN OrderLine lin ON lin.SO = hdr.SO

If you don't have line numbers or want to print the orderlines in a different order than in that of orderlines, it is quite easy to modify the subquery accordingly... as long as there is a way to order the lines uniquely (without ties). If there isn't such a way, you'll probably have to add such column.

jake greene Grasshopper Points: 12 More actions · Answer 2

Hi Vladan,

Thanks for your post and sample code. I will test this and confirm, but I think this will work without issue. I was unfamiliar with Case, but have since researched and this appears the way to go.

Again, thanks for your assistance.

Jake