Group by I formatiranje outputa

[ 01011011 @ 05.08.2008. 21:24 ] @

Select group by

imam sledeca polja kad uradim SELECT

Code:

DAY                 TIME
Sunday               Closed
Monday               8:00AM-5:00PM
Tuesday              8:00AM-5:00PM
Wednesday            8:00AM-5:00PM
Thursday             8:00AM-5:00PM
Friday               8:30AM-5:00PM
Saturday             Closed

E sad sta treba da uradim je to je trenutno sto dobijam iz baze podataka, a meni je potrebno da se uradi GROUP BY i da mi ovako izgleda ovaj specifican recordset

Code:

Mon, Tue, Wed & Thu          8:00AM-5:00PM
Fri                          8:30AM-5:00PM
Sat & Sun                    Closed

Da li je ovo moguce odraditi i kako?

[ adopilot @ 05.08.2008. 23:02 ] @

Ja sam to uspijeo riještit isto taku uz pomoć ovog foruma.
Koristili smo pretvranje podataka u XML
dok ne napravim konačno riješenje pogledaj te temu
http://www.elitesecurity.org/t...smijestanje-vise-rekorda-jedan

[ miq357 @ 05.08.2008. 23:42 ] @

Ako recimo početna tabela izgleda ovako:

Code:

vreme            vrv    dan
Closed             1     Sunday
8:00AM-5:00PM     1    Monday
8:00AM-5:00PM     2    Tuesday
8:00AM-5:00PM     3    Wednesday
8:00AM-5:00PM     4    Thursday
8:30AM-5:00PM     1    Friday
Closed             2    Saturday

Za ovakav odgovor:

Code:

8:00AM-5:00PM      Monday, Tuesday, Wednesday, Thursday
8:30AM-5:00PM      Friday
Closed              Sunday, Saturday

Rešenje bi bilo:

Code:

  WITH X (vreme, CNT, lista, vrv, LEN)
      AS (
SELECT vreme, COUNT(*) OVER (PARTITION BY vreme),
           CAST(dan AS VARCHAR(100)), vrv, 1
  FROM test
UNION ALL
SELECT X.vreme, X.CNT,
           CAST(X.lista + ', ' + E.dan AS VARCHAR (100)),
           E.vrv, X.LEN+1
   FROM test E, X
WHERE E.vreme = X.vreme AND E.vrv > X.vrv
         )
SELECT vreme, lista
  FROM X
WHERE LEN = CNT
ORDER BY 1

Ovo je ovako na brzinu, moglo bi to verovatno i bolje da se izvede. U početni uslov je dodata još jedna kolona (vrv) jer sam ovo već imao ovako rešeno, ali mislim da je vrlo približno traženom i dosta za početak

[ 01011011 @ 06.08.2008. 15:27 ] @

Ne ide mi ovako, pa sam odradio u C# Unutar koda sam napravio liste u koje sam ubacio ovo i sortirao kako mi treba.

Hvala na odgovorima :)

[ adopilot @ 07.08.2008. 14:29 ] @

@miq357
Dobra je i ova sa WITH klauzulom ali kompikovana za shvatit

A može se i riješiti na ovaj način

Code:

/*-- podatci iz primjera --*/
create  table #test
(vreme   varchar(max),vrv varchar(max),dan varchar(max))

insert into #test
select 'Closed',             '1','Sunday'
union all
select '8:00AM-5:00PM','1','Monday'
union all
select '8:00AM-5:00PM','2','Tuesday'
union all
select '8:00AM-5:00PM','3','Wednesday'
union all
select '8:00AM-5:00PM','4','Thursday'
union all
select '8:30AM-5:00PM','1','Friday'
union all
select 'Closed','2','Saturday'

Code:

select
(
select (left((dan),3)+', ') as [data()]
from #test t
where t.vreme=b.vreme
order by t.dan
for xml path('')
) as ado
,b.vreme
from #test b
group by b.vreme

Možda je ova galerija bogdu jednostavnija
Lijep pozdrav