sql server msde: You basically want to xtab the data. Try:

http://support.microsoft.com/kb/175574
or
http://www.sqlteam.com/item.asp?ItemID=2955
or
http://www.rac4sql.net/


--
-oj


[quoted text, click to view]

If the number of subkeys is known or fix, then you can create a view to join
with header_table.

Example:

use northwind
go

create table h(colA int, colB varchar(25))
create table t(colA int, colB int, colC int)
go

insert into h values(1, 'header 1')
insert into h values(2, 'header 2')

insert into t values(1, 1, 10)
insert into t values(1, 2, 20)
insert into t values(1, 3, 30)
insert into t values(2, 1, 15)
insert into t values(2, 2, 17)
insert into t values(2, 3, 19)
go

create view myview
as
select
coalesce(a.colA, b.colA, c.colA) as colA,
sum(a.colC) as colB,
sum(b.colC) as colC,
sum(c.colC) as colD
from
(
select colA, colC from t where colB = '1'
) as a
full outer join
(
select colA, colC from t where colB = '2'
) as b
on 0 = 1
full outer join
(
select colA, colC from t where colB = '3'
) as c
on 0 = 1
group by
coalesce(a.colA, b.colA, c.colA)
go

select
*
from
h
inner join
myview as m
on h.colA = m.colA
order by
h.colA
go

drop view myview
go

drop table h, t
go


AMB

[quoted text, click to view]