TSQL malo komplikovaniji query

[ surferxyz @ 08.08.2006. 08:35 ] @

Zdravo svima.

Problem je sledeći.

Imam tabele (SQL 2000) Poruke(PorukaID, poruka), Statusi(StatusID,status) i StatusiPoruka(PorukaID, StatusID)
Pretpostavimo da imamo ove podatke

Poruke
------------
1 - Poruka1
2 - poruka2

Statusi
-------------------
1 - Status1
2 - Status2
3 - Status3

StatusiPoruka
--------------------
1 - 1
1 - 3
2 - 2

Kako napisati upit da bih dobio sledeći rezultat

PorukeSaStatusim

PorukaID ...... poruka .......... Status1 ........ Status2 ...... Status3
------------- ----------- ------------ ----------- ----------
1 .................. Poruka1 ............. 1 ............. 0 ............. 1
2 .................. poruka2 ............. 0 ............. 1 ............. 0

Nikako ne mogu da savladam Pivot i CrosTable :)

Unaprijed hvala.

PS.
U pitanju je SQL server 2000

[ goranvuc @ 08.08.2006. 11:06 ] @

Ne postoji PIVOT u TSQL-u, tako da ti ostaje jedino da dinamicki kreiras SQL u Stored Procedure.

Znaci, treba ti X kolona za X pojava vrednosti statusa - evo kako treba da ti izgleda krajnji SQL za 3 vrednosti statusa, nemam vremena da ti pisem SP koja ce ti ga sastaviti:

Code:

SELECT Poruke.PorukaID, Poruka, Status1, Status2, Status3
FROM Poruke
LEFT JOIN (SELECT COUNT(PorukaID) AS Status1, PorukaID FROM StatusiPoruka WHERE StatusID = 1 GROUP BY PorukaID) AS Q1 ON Poruke.PorukaID = Q1.PorukaID
LEFT JOIN (SELECT COUNT(PorukaID) AS Status2, PorukaID FROM StatusiPoruka WHERE StatusID = 2 GROUP BY PorukaID) AS Q2 ON Poruke.PorukaID = Q2.PorukaID
LEFT JOIN (SELECT COUNT(PorukaID) AS Status3, PorukaID FROM StatusiPoruka WHERE StatusID = 3 GROUP BY PorukaID) AS Q3 ON Poruke.PorukaID = Q3.PorukaID

pa, analogno ovom gore za 4 vrednosti statusa

Code:

SELECT Poruke.PorukaID, Poruka, Status1, Status2, Status3, Status4
FROM Poruke
LEFT JOIN (SELECT COUNT(PorukaID) AS Status1, PorukaID FROM StatusiPoruka WHERE StatusID = 1 GROUP BY PorukaID) AS Q1 ON Poruke.PorukaID = Q1.PorukaID
LEFT JOIN (SELECT COUNT(PorukaID) AS Status2, PorukaID FROM StatusiPoruka WHERE StatusID = 2 GROUP BY PorukaID) AS Q2 ON Poruke.PorukaID = Q2.PorukaID
LEFT JOIN (SELECT COUNT(PorukaID) AS Status3, PorukaID FROM StatusiPoruka WHERE StatusID = 3 GROUP BY PorukaID) AS Q3 ON Poruke.PorukaID = Q3.PorukaID
LEFT JOIN (SELECT COUNT(PorukaID) AS Status4, PorukaID FROM StatusiPoruka WHERE StatusID = 4 GROUP BY PorukaID) AS Q4 ON Poruke.PorukaID = Q4.PorukaID

Nadam se da si na ovo mislio.

[ chachka @ 08.08.2006. 11:17 ] @

Code:

SELECT p.PorukaID, p.Poruka, MAX(s.Status1), MAX(s.Status2), MAX(s.Status3)
  FROM Poruke AS p
       JOIN (SELECT sp.PorukaID,
                    CASE WHEN sp.StatusID = 1 THEN 1 ELSE 0 END AS Status1,
                    CASE WHEN sp.StatusID = 2 THEN 1 ELSE 0 END AS Status2,
                    CASE WHEN sp.StatusID = 3 THEN 1 ELSE 0 END AS Status3
               FROM StatusiPoruka AS sp) AS s
         ON p.PorukaID = s.PorukaID
GROUP BY p.PorukaID, p.Poruka
ORDER BY p.PorukaID

PIVOT je samo skraceni nacin zapisivanja gornjeg upita, a ja sam zaboravio kako se koristi :)

Tabela 'Statusi' se u upitu ne koristi. Ako se u tabelu 'Statusi' dodaje nov status onda se i gornji upit mora menjati. Zbog toga se ovakve forme izvestaja prepustaju alatima za generisanje izvestaja.

[ goranvuc @ 08.08.2006. 11:24 ] @

Da li tebi treba ukupan broj pojava odredjenog statusa po PorukaID ili samo (0, 1)? Ako ti treba ovo prvo onda ti samo moj predlog "radi stvar", a ako je samo 1 ili 0 onda moze i ovo sto ti chachka predlaze. Inace, PIVOT u nekim varijantama SQL-a postoji kao naredba (MS Access Jet npr.).

[ chachka @ 08.08.2006. 11:33 ] @

Ako treba broj pojavljivanja odredjenog statusa onda se umesto MAX stavi SUM

Code:

SELECT p.PorukaID, p.Poruka, SUM(s.Status1), SUM(s.Status2), SUM(s.Status3)
  FROM Poruke AS p
       JOIN (SELECT sp.PorukaID,
                    CASE WHEN sp.StatusID = 1 THEN 1 ELSE 0 END AS Status1,
                    CASE WHEN sp.StatusID = 2 THEN 1 ELSE 0 END AS Status2,
                    CASE WHEN sp.StatusID = 3 THEN 1 ELSE 0 END AS Status3
               FROM StatusiPoruka AS sp) AS s
         ON p.PorukaID = s.PorukaID
GROUP BY p.PorukaID, p.Poruka
ORDER BY p.PorukaID

[ surferxyz @ 08.08.2006. 11:44 ] @

hvala.

U biti to je to.
Ne znam unaprijed broj statusa, tako da mi upit ne može biti unaprijed predefinisan, al ga mogu dinamički kreitati i izvršiti.
Mislio sam da postoji elegantnije rješenje ovog ptoblem.
U SQL 2005 bi sve bilo puno drugačije.

HVALA.