Gegroepeerde resultaten in kolommen (transpose)

[thomascook]

Hallo,

Ik ben op zoek naar een query die het volgende bereikt:

De status van het grootste eventid, per productid in een aparte kolom.
Maar indien een productid de status 3 heeft (in de hele tabel) dan moet de status voor dat productid altijd 0 zijn.

Stel ik heb de volgende tabel:

EventId ProductId Status
200000 a 0
200002 a 3
200008 a 2
200010 a 1
200013 a 1
200001 b 1
200003 b 0
200007 b 0
200012 b 0
200004 c 0
200005 c 3
200009 c 0
200011 c 0
200006 d 1

dan wordt de uitkomst:

a b c d
0 0 1 1

Ik hoop dat ik het een beetje duidelijk heb uitgelegd en dat iemand me kan helpen.

Alvast bedankt!

Groet,
Thomas

 

[Arjee]

Select T.EventID, T.ProductId, (IIF( E.Maxstatus = '3','0',E.Status)) as status
From TBLTable T,
(Select M.Eventid as EventID, T.Status as status, M.Status as Maxstatus
From TBLTable T,
(Select ProductId, Max(EventId) as EventId, Max(Status) as Status
From TBLTable T
Group by ProductID) M
Where T.EventID = M.EventID) E
Where T.EventID = E.EventID

Dit levert een tabel op:
EventID, ProductID, Status
200013,a,0
200012,b,0
200011,c,0 (er zit een 3 in product C dus status = 0)
200006,d,1

Wil je dit transposen dan moet je dat veld voor veld definieren. Dat kan in dit geval dus alleen als je producten constant zijn. Zodra er een product bij komt moet je de query aanpassen. Je krijgt dan iets als

Select a.Status as A, b.status as B, C.status as C, D.status as D
From
(Select (IIF( E.Maxstatus = '3','0',E.Status)) as status
From TBLTable T,
(Select M.Eventid as EventID, T.Status as status, M.Status as Maxstatus
From TBLTable T,
(Select ProductId, Max(EventId) as EventId, Max(Status) as Status
From TBLTable T
Group by ProductID
Having ProductID = 'a') M
Where T.EventID = M.EventID) E
Where T.EventID = E.EventID) A,
(Select (IIF( E.Maxstatus = '3','0',E.Status)) as status
From TBLTable T,
(Select M.Eventid as EventID, T.Status as status, M.Status as Maxstatus
From TBLTable T,
(Select ProductId, Max(EventId) as EventId, Max(Status) as Status
From TBLTable T
Group by ProductID
Having ProductID = 'b') M
Where T.EventID = M.EventID) E
Where T.EventID = E.EventID) B,
(Select (IIF( E.Maxstatus = '3','0',E.Status)) as status
From TBLTable T,
(Select M.Eventid as EventID, T.Status as status, M.Status as Maxstatus
From TBLTable T,
(Select ProductId, Max(EventId) as EventId, Max(Status) as Status
From TBLTable T
Group by ProductID
Having ProductID = 'c') M
Where T.EventID = M.EventID) E
Where T.EventID = E.EventID) C,
(Select (IIF( E.Maxstatus = '3','0',E.Status)) as status
From TBLTable T,
(Select M.Eventid as EventID, T.Status as status, M.Status as Maxstatus
From TBLTable T,
(Select ProductId, Max(EventId) as EventId, Max(Status) as Status
From TBLTable T
Group by ProductID
Having ProductID = 'd') M
Where T.EventID = M.EventID) E
Where T.EventID = E.EventID) D

Mogelijk kan het nog eenvoudiger. Ik vraag me overigens wel af waarom je een tabel zou willen die maximaal 1 record kan bevatten.

Groet en succes
arjee

 

[thomascook]

Eén record met meerdere kolommen.

Hi Arjee,

Bedankt voor je supersnelle antwoord! :thumb:

Ik gebruik deze query in een dashboard tool. Ik koppel iedere kolom uit de query aan een KPI op het dashboard, vandaar dat ik slechts één record terug mag krijgen.
De productentabel is redelijk statisch, dus dat is geen probleem.

Ik heb de volgende query om de kolommen te maken, maar ik krijg deze niet gekoppeld aan jouw eerste query. Misschien weet jij een oplossing.

select count(EventId) As TOTAAL,
sum(FIND_IN_SET(ProductID,'a')) ProductA,
sum(FIND_IN_SET(ProductID,'b')) ProductB,
sum(FIND_IN_SET(ProductID,'c')) ProductC,
sum(FIND_IN_SET(ProductID,'d')) ProductD,
from <TABEL>
order by TOTAL;

Groet,
Thomas

 

[Arjee]

Hallo Thomas,

Ben nu wel een beetje benieuwd wat dat voor een dashboard tool is...

Voor je nieuwe vraag ben ik bang dat ik je niet verder kan helpen. Het find_in_set statement heb ik nog nooit van gehoord. Ik vermoed dat jein die vier statements moet verwijzen naar de eerste query die ik gegeven heb.

Succes