create table #art(ID int,     pgID    int, artnr varchar(10))
insert #art values(            1       , 10      , '2n3055    ')
insert #art values(            2       , 20      , '7400')
insert #art values(            3       , 10      , 'BC107')


create table #artdata(    artikelID   int, pgfeldID    int, value real)
insert #artdata values(            1           ,1           ,110)
insert #artdata values(            1           ,2           ,0.09)
insert #artdata values(            1           ,3           ,1.8)
insert #artdata values(            2           ,5           ,1000)
insert #artdata values(            2           ,4           ,5)
insert #artdata values(            1           ,1           ,0.3)
insert #artdata values(            1           ,2           ,0.05)
insert #artdata values(            1           ,3           ,0.1)

create table #pgfelder    (ID      int, pgID    int, name varchar(10))
insert #pgfelder values(            1       ,10      ,'Ptot')
insert #pgfelder values(            2       ,10      ,'Ib')
insert #pgfelder values(            3       ,10      ,'Icmax')
insert #pgfelder values(            4       ,20      ,'fmax')
insert #pgfelder values(            5       ,20      ,'Vcc')

create table #produktgr   (ID      int, name varchar(10))
insert #produktgr values(            10      , 'transistor       ')
insert #produktgr values(            20      , 'ic')

select *
 from #art t1 
left join #artdata t2 on t1.ID = t2.artikelID
left join #pgfelder t3 ON t2.pgfeldID = t3.ID 
left join #produktgr t4 ON t3.pgID = t4.ID and t1.pgID = t4.ID
where t4.name = 'transistor'


select t1.ID, t1.PgID, t1.artnr,
        max(case when t3.ID = 1 then t2.value end) as Ptot,
        max(case when t3.ID = 2 then t2.value end) as Ib,
        max(case when t3.ID = 3 then t2.value end) as Icmax,
        max(case when t4.ID = 4 then t2.value end) as fmax,
        max(case when t4.ID = 5 then t2.value end) as vcc
 from #art t1 
join #artdata t2 on t1.ID = t2.artikelID
join #pgfelder t3 ON t2.pgfeldID = t3.ID 
join #produktgr t4 ON t3.pgID = t4.ID
where t4.name = 'transistor'
group by t1.ID, t1.PgID, t1.artnr
having max(case when t3.ID = 1 then t2.value end) > 75


select t1.ID, t1.PgID, t1.artnr,
        case when t3.ID = 1 then t2.value end as Ptot,
        case when t3.ID = 2 then t2.value end as Ib,
        case when t3.ID = 3 then t2.value end as Icmax,
        case when t3.ID = 4 then t2.value end as fmax,
        case when t3.ID = 5 then t2.value end as vcc
 from #art t1 
join #artdata t2 on t1.ID = t2.artikelID
join #pgfelder t3 ON t2.pgfeldID = t3.ID 
join #produktgr t4 ON t3.pgID = t4.ID
where t4.name = 'transistor'
group by t1.ID, t1.PgID, t1.artnr, t3.id, t2.value

This page was last updated on May 01, 2006 04:28 PM.