Zidar Canada
Član broj: 15387 Poruke: 3085 *.100.46-69.q9.net.
|
Mozda je malo kasno, ali evo resenja bez kursora. Resenje je u MS SQL ali mi se cini da Firebir moze sve sto moze i MS SQL i malo vise, pa vredi pokazati barem princip.
/* Ovako moze u MS SQL, bez kursora
Prvo da napravimo tabelu:
*/
CREATE TABLE CheckPoints
(
Datum DateTime NOT NULL
, ChkPoint int NOT NULL
)
-- Dodamo neke test vrednosti:
INSERT INTO CheckPoints (Datum, ChkPoint) VALUES ('07.02.2006',768)
INSERT INTO CheckPoints (Datum, ChkPoint) VALUES ('07.02.2006',923)
INSERT INTO CheckPoints (Datum, ChkPoint) VALUES ('08.02.2006',695)
INSERT INTO CheckPoints (Datum, ChkPoint) VALUES ('08.02.2006',1100)
SELECT * FROM CheckPoints
Datum ChkPoint
----------------------- -----------
2006-07-02 00:00:00.000 768
2006-07-02 00:00:00.000 923
2006-08-02 00:00:00.000 695
2006-08-02 00:00:00.000 1100
(4 row(s) affected)
--- Ovako se vrsi konkatenacija (u principu):
DECLARE @CheckPoints AS varchar(50)
-- treba varchar(size)toliki da zadovolji najgori moguci slucaj
-- 50 sam uzeo zbog lepseg stampanja a dovoljno je za ovaj test
SET @CheckPoints = ''
SELECT
@CheckPoints = @CheckPoints + CAST(ChkPoint AS varchar) + ','
FROM CheckPoints
SELECT @CheckPoints AS [Concatenated ChKPoint]
/*
Concatenated ChKPoint
--------------------------------------------------
768,923,695,1100,
Spojili smo sve u jedan string, i bez kursora. Trik je u cinjenici da
se dodeljivanje vrednosti varijabli preko SELECT statementa moze raditi
i kad SELECT vraca vis redova.
Na primer, ako
SELECT STudentID FROM Students
vraca 50 redova i mi probamo
DECLARE @StudentID int
SELECT @StudentID = StudentID FROM Students
promenljiva @StudentID imace vrednost poslednjeg reda. prvih 49 bice ignorisano.
Koristeci taj princip, izraz
@CheckPoints = @CheckPoints + CAST(ChkPoint AS varchar) + ','
ce procitati vrednost ChkPoint za svaki red, i dodace ga varijabli @CheckPoints,
a sve ce biti odvojeno znakom zareza ','
Madjutim, nismo dobili bas ono sto se trazi, dobili smo konkatenaciju svih redova,
a nama treba po datumima.
*/
-- Kako dakle uraditi po datumina? GROUP BY nam nece pomoci (ja bar ne znam da napravim tako)
-- treba nam datum u WHERE u naredbi koja vrsi concatenaciju
BEGIN
DECLARE
@CheckPoints AS varchar(8000)
, @Datum AS DateTime
SET @CheckPoints = ''
SELECT
@Datum = Datum
, @CheckPoints = @CheckPoints
+ CAST(ChkPoint AS varchar)
+ ','
FROM CheckPoints
WHERE Datum = '2006-07-02'
---- Datum moze da bude parametar u Stored procedure ili funkciji
-- rezultat:
SELECT
@Datum AS Datum
, LEFT(@CheckPoints,LEN(@CheckPoints)-1)
AS [CheckPoints] --- neam vise zareza na kraju ;-)
Datum CheckPoints
----------------------- --------------
2006-07-02 00:00:00.000 768,923
(1 row(s) affected)
END
--- Ako nam treba za svaki datum,
---- treba nam neki LOOP idemo kroz sve datume.
---- ne mogu da se setim nicega pametnijeg od ovoga:
GO
DECLARE
@MinDatum AS DateTime
, @CurrentDatum AS DateTime
SET @MinDatum =(SELECT MIN(Datum) FROM CheckPoints)
SET @CurrentDatum = @MinDatum
WHILE @CurrentDatum IS NOT NULL
-- EXISTS (SELECT * FROM CheckPoints WHERE Datum = @CurrentDatum)
BEGIN
DECLARE
@CheckPoints AS varchar(8000)
, @Datum AS DateTime
SET @CheckPoints = ''
SELECT
@Datum = Datum
, @CheckPoints = @CheckPoints
+ CAST(ChkPoint AS varchar)
+ ','
FROM CheckPoints
WHERE Datum = @CurrentDatum
SELECT
@Datum AS Datum
, LEFT(@CheckPoints,LEN(@CheckPoints)-1)
AS [CheckPoints]
--- Idemo na sledeci datum
SET @CurrentDatum = (SELECT MIN(Datum)
FROM CheckPoints
WHERE Datum > @CurrentDatum)
END
GO
Izlaz je ovakav:
Datum CheckPoints
----------------------- -------------
2006-07-02 00:00:00.000 768,923
(1 row(s) affected)
Datum CheckPoints
----------------------- --------------
2006-08-02 00:00:00.000 695,1100
(1 row(s) affected)
Ali mi bi hteli ovo:
Datum CheckPoints
----------------------- --------------
2006-07-02 00:00:00.000 768,923
2006-08-02 00:00:00.000 695,1100
(2 row(s) affected)
Za ovo, predlazem da napravite stored proceduru ili funkciju,
pa joj posaljete parametre @FromDate i @ToDate.
Onda unutar procdure kreirate temp tabelu sa dve kolone (Datum, CheckPoints).
Onda tabelu napunite kroz WHILE loop.
Na karju iz procedure vratite SELECT * FROM #TempTabela
Da li je ovo brze i jednostavnije nego kursor? Nisdam siguran,
ali mi se dopao princip konkatenacije upotrebom metode
SELECt @varijabla = .. FROM myDataSet
|