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.