January 16, 2008 at 5:47 pm
Here is my situation:
One table has saletransactions (my header table (HT))...the other table has the items bought in that salestransaction (my detail table(DT))
I want to combine the two tables in one view, with the header being the first line and having fields 'a', 'b', 'c' and the next lines being drawn from the DT containing fields 'd', 'e', 'f'
Both tables are joined by a key.
So if we had a transaction that sold 3 items, my view would look like this
a b c
d e f
d e f
d e f
Any way to do this ?..am I clear enough ?
January 16, 2008 at 7:20 pm
I'm not sure about a view, since the ORDER BY is critical in this piece.
That being said, you could user something like this
select f1,f2,f2
from
(select a as f1,
b as f2,
c as f3,
salesTransactionKey,
0 as rank
from HT
UNION ALL
select d,
e,
f,
salesTransactionKey,
1 as rank
from DT
) as Combined
ORDER BY salesTransactionKey, rank
You will need to make sure that
a and d are the same data type,
b and e same,
and c and f same,
or you will get errors.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 8:30 pm
Nice sample Matt.
Are you using sql2k5? If so take a look at BOL at a recursive CTE's (Common table expressions).
January 16, 2008 at 9:08 pm
All due respect, but - why would you do that to such a nice problem?
I just don't see what the recursize part would do to help....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply