GoldenLine
Zaloguj się
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Dynamiczna tabela

Witam,
Czy macie moze pomysl na tworzenie w procedurze tabeli tymczasowej z rozna iloscia kolumn? (MSSQL)
Moze na przykladzie bedzie mi latwiej przedstawic problem.
Dostepnych mam 20 projektow.
Raport ma pokazac pracownikow przydzielonych do poszczegolnych projektow (pracownik moze byc przydzielony do wiecej niz jednego projektu). Projekty bez przydzialu pracownikow nie powinny sie pokazac na raporcie.
Istotnym jest, nazwa kolumny byla nazwa projektu.
Raz wiec raport moze wygladac tak:
NAZWISKO PROJ1 PROJ2 PROJ3
Kowalski x x
Nowak x x
Janko x

A innym razem tak:
NAZWISKO PROJ1 PROJ5 PROJ7 PROJ15
Kowalski x x
Nowak x x
Janko x x
Bulka x
w zaleznosci od okreslen czasowych

Wdzieczna za wszelkie sugestie.
PozdrawiamEdyta Ratajczyk edytował(a) ten post dnia 30.11.07 o godzinie 09:03
Link do wypowiedziLink
Mirosław Serwaczyński

Mirosław Serwaczyński Analityk programista

Temat: Dynamiczna tabela

Spróbuj może tak:

DECLARE @sql as varchar(1000) lub ile tam trzeba

W tym @sql budujesz komendę "CREATE TABLE ...." i w jakiejś pętli robisz SET @sql=@sql + "kolejne pole"

potem wykonujesz: exec (@sql)
no i napełniasz powstałą tabelę nazwiskami.

pozdrawiam
Link do wypowiedziLink
Łukasz Krówczyński

Łukasz Krówczyński Główny
Specjalista-IT, Bank
BPH S.A.

Temat: Dynamiczna tabela

Nie wiem czy dobrze rozumiem problem, ale przychodzi mi na myśl takie polecenie:

Select kol1, kol2, kol3 INTO tabela_tymczasowa FROM tabela_zrodlowa;

W PostgreSql działa - nie wiem czy również i w MSSQLu.

Pozdrawiam
Link do wypowiedziLink
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Dynamiczna tabela

Mirosław Serwaczyński:
Spróbuj może tak:

DECLARE @sql as varchar(1000) lub ile tam trzeba

W tym @sql budujesz komendę "CREATE TABLE ...." i w jakiejś pętli robisz SET @sql=@sql + "kolejne pole"

potem wykonujesz: exec (@sql)
no i napełniasz powstałą tabelę nazwiskami.

Hmmm... Chodzi o to, że tabela sama w sobie ma też być dynamiczna.

Declare @Raport table
(id_osoba decimal(20,0),
imie_nazwisko nvarchar(255),
proj1 varchar(10),
proj2 varchar(10),
proj3 varchar(10),
)
albo inaczej... właśnie o to, żeby to proj1, proj2 itd. mogło być dowolnym projem w dowolnej konfiguracji.
Może nie potrzebnie kombinuję...
Na podstawie jedengo zapytania wiem, jakie kolumny będą, na podstawie kolejnego - wypełniam. (Taki jest plan ;))

Łukasz - ja nie wiem właśnie które to będą kol1, kol2... To są dowolne spośród n (w przykładzie - 20)
Pozdrawiam
Link do wypowiedziLink
Łukasz Krówczyński

Łukasz Krówczyński Główny
Specjalista-IT, Bank
BPH S.A.

Temat: Dynamiczna tabela

Tak - to są dowolne kolumny w dowolnej konfiguracji oczywiście zawarte w tabeli tabela_zrodlowa (podanej po FROM).

INTO działa w taki sposób, że po wykonaniu select'a dane wynikowe są zapisywane od razu do tabeli o nazwie wskazanej po INTO. Tabela jest tworzona automatycznie, a nazwy kolumn są tożsame z nazwami kolumn tabeli źródłowej (tej, która była wskazana po FROM).

Taka tabela 'żyje' sobie normalnie - usuwa się ją jak każdą zwykłą tabelę.

Tylko jak podkreślam jest to polecenie z którego korzystam w ramach PostgreSQL i nie wiem czy jest dostępne w MSSQL.

http://www.postgresql.org/docs/8.2/interactive/sql-sel...

Pozdrawiam
Link do wypowiedziLink
Robert Żwirski

Robert Żwirski programista, Black
Red White S.A.

Temat: Dynamiczna tabela

Gdybym robił to na Oracle'u - uzyłbym zmiennej tablicowej i dynamicznego sql-a (do utworzenia tablicy) - nie wiem czy cos takiego jest w MSSQL. Jesli jesteś zainteresowana to moge opisac to szerzej, nie weim tylko czy zadziała to w MSSQL
Link do wypowiedziLink
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Dynamiczna tabela

Robert Żwirski:
Gdybym robił to na Oracle'u - uzyłbym zmiennej tablicowej i dynamicznego sql-a (do utworzenia tablicy) - nie wiem czy cos takiego jest w MSSQL. Jesli jesteś zainteresowana to moge opisac to szerzej, nie weim tylko czy zadziała to w MSSQL

Czy to to, co sugerował Mirosław?
Wolałabym nie tworzyć tabeli na serwerze, dlatego chodzi mi o zmienną (jak pisałam)
Pisz, pisz.

Łukasz:
Nie mogę zastosować czystego insert .. into...
Dane źródłowe nie mają tych kolumn - to pierwsze primo.
Ja potrzebuję stworzyć tabelę na podstawie danych:
ŹRÓDŁO:
Pracownik1 Projekt1 2007-11-30
Pracownik2 Projekt1 2007-11-30
Pracownik5 Projekt2 2007-10-30
Pracownik3 Projekt3 2007-10-30
Pracownik3 Projekt5 2007-11-30
Pracownik1 Projekt4 2007-10-30

procedura którą tworzę, ma mi podać dla np. 2007-11-30
Nazwisko Projekt1 Projekt5
Pracownik1 x -
Pracownik2 x -
Pracownik3 - x
dla 2007-0-30 natomiast wynik wyglądałby tak:
Nazwisko Projekt2 Projekt3 Projekt4
Pracownik5 x - -
Pracownik3 - x -
Pracownik1 - - x
Jak widać, nie wiem, które projekty wejdą jako kolumny dopóki o to nie zapytam. Jak zapytam - to dopiero będę mogła szaleć.
Chyba nie pomieszałam. Chodzi o stworzenie tabeli (najchętniej w procedurze jako zmiennej) i wypełnienie jej w zależności od danych.
Ufff...
Link do wypowiedziLink

konto usunięte

Temat: Dynamiczna tabela

Problem w tym, że mieszasz prezentację z przetwarzaniem danych. Zakładając, że taki jest wymóg (a nie czyjeś widzi-misie) to można zrobić tak (bo to coś ala pivot table):

http://www.sqlteam.com/article/dynamic-cross-tabs-pivo...

Inne rozwiązanie to po prostu wygenerowanie projektu jako jednej kolumny a potem przetwarzanie tego na kliencie (prezentacja).
Link do wypowiedziLink
Grzegorz G.

Grzegorz G. ASE / Systems
Architect, Syniverse

Temat: Dynamiczna tabela

Chodzi o to, zeby napisać pivot-query. Skoro nie wiesz ile będzie kolumn - trzeba to zrobić dynamicznie. Czyli lecisz po wszystkich unikalnych projektach w danym przedziale czasu i generujesz zapytanie. Mając zapytanie - możesz zrobić z nim co chcesz, np. dodać klauzulę
into #tmp
i jest Twoja tabelka.
(przykład w oracle'u, nietrudno go przerobić na t-sql)

-- stmt - tu będzie wynikowe zapytanie
-- start_dat i end_dat - ograniczniki czasowe
var stmt varchar2(4000)
var start_dat varchar2(20)
var end_dat varchar2(20)

-- takie mam dane w tabelce
SQL> select * from aaa;

EMP PROJECT DAT
---------- ------------------------------ ---------
Pracownik1 Projekt1 30-NOV-07
Pracownik2 Projekt1 30-NOV-07
Pracownik5 Projekt2 30-OCT-07
Pracownik3 Projekt3 30-OCT-07
Pracownik3 Projekt5 30-NOV-07
Pracownik1 Projekt4 30-OCT-07

-- ustawiam daty
exec :start_dat := '30/11/2007';
exec :end_dat := '30/11/2007';

-- i odpalam
begin
:stmt := 'select emp';
for x in (select distinct project from aaa where dat between to_date(:start_dat, 'DD/MM/YYYY') and to_date(:end_dat, 'DD/MM/YYYY')) loop
:stmt := :stmt || ', max(case when project = ''' || x.project || ''' then 1 else 0 end) as "' || x.project || '"';
end loop;
:stmt := :stmt
|| ' from aaa '
|| ' where dat between to_date(''' || :start_dat || ''', ''dd/mm/yyyy'') and to_date(''' || :end_dat || ''', ''dd/mm/yyyy'')'
|| ' group by emp';
end;
/

to daje zmienną :stmt która po sformatowaniu wygląda

select emp,
max(case when project = 'Projekt1' then 1 else 0 end) as "Projekt1",
max(case when project = 'Projekt5' then 1 else 0 end) as "Projekt5"
from aaa
where dat between to_date('30/11/2007', 'dd/mm/yyyy') and to_date('30/11/2007', 'dd/mm/yyyy')
group by emp

I wynik jest

EMP Projekt1 Projekt5
---------- ---------- ----------
Pracownik1 1 0
Pracownik2 1 0
Pracownik3 0 1

Po przestawieniu daty

exec :start_dat := '30/10/2007';
exec :end_dat := '30/10/2007';

Otrzymujemy

select emp,
max(case when project = 'Projekt2' then 1 else 0 end) as "Projekt2",
max(case when project = 'Projekt3' then 1 else 0 end) as "Projekt3",
max(case when project = 'Projekt4' then 1 else 0 end) as "Projekt4"
from aaa
where dat between to_date('30/10/2007', 'dd/mm/yyyy') and to_date('30/10/2007', 'dd/mm/yyyy')
group by emp

Co zawraca

EMP Projekt2 Projekt3 Projekt4
---------- ---------- ---------- ----------
Pracownik5 1 0 0
Pracownik3 0 1 0
Pracownik1 0 0 1
Link do wypowiedziLink
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Dynamiczna tabela

Dziękuję wszystkim. W poniedziałek będę próbowała dalej.
Pozdrawiam
Link do wypowiedziLink
Robert Żwirski

Robert Żwirski programista, Black
Red White S.A.

Temat: Dynamiczna tabela

myslałem o kursorze pobierającym pracownika i projekt i zapełniającym zmienną tablicową (bulk collect) następnie w pierwszej pętli for zostały by pobrane kolumny do utworzenia tablicy tymczasowej a po jej utworzeniu, w drugiej pętli tablica zoatała by wypełnieona. Jednak sposób przedstawiony przez kol. Grzegorza jest prostszy a przez to lepszy :) pozdrawiam
Link do wypowiedziLink
Edyta Ratajczyk

Edyta Ratajczyk
programista/bazodano
wiec/analityk
biznesowy IT

Temat: Dynamiczna tabela

Witam.
Po rozwiązaniu problemu piszę jak :) Ku pamięci a i może komuś innemu się przyda... (będę się mniej więcej przykładem posługiwała, chociaż jak na niego patrzę, to nie do końca trafiony...
Stworzyłam tabelę tymczasową #pracownicy_projekty (nazwisko varchar(30))
Kursorem pobudowałam @sqle 'Alter table #pracownicy_projekty add ' + @nazwa_projektu + ' varchar(25)
Execute sp_executesql @sql
i dalej już wypełnianie danych.
Zaskoczeniem dla mnie było - dlatego tu piszę - że wrzucenie całego create table... do @sql i odpalenie spowodowało utworzenie tabeli ale widocznej TYLKO wewnątrz tego 'EXECUTE'.
Ponieważ nie chciałam całej procedury (która jest trochę bardziej skomplikowana niż przykład) zamykać w zmienną tekstową (a propos - ktoś wie jak obejść ograniczenie zmiennej powyżej 4000 znaków?) musiałam w ten sposób.
Zamieszałam? Hmmm... Ale może - jak ktoś się będzie zastanawiał, czemu nie działa
set @sql='create table przyklad'
execute sp_executesql @sql
select * from przyklad
to już wie :D
Pozdrawiam i dziękuję wszystkim raz jeszcze
Link do wypowiedziLink
Anna Pietruczuk

Anna Pietruczuk starszy konsultant
d/s serwisu /
wdrożeń, SIMPLE S.A.

Temat: Dynamiczna tabela

serdecznie dziękuję za poradę, by stworzyć tabelkę tymczasową normalnie a executem alterować. Uff, wdzięcznam bardzo.
Pozdrawiam.
Link do wypowiedziLink
Monika Mitura

Monika Mitura Oracle Developer,
Pretius

Temat: Dynamiczna tabela

Tak z ciekawości, czemu rozwiazaniem ma być tabela tymczasowa a nie SELECT czy view z PIVOTEM ?
m
Link do wypowiedziLink
Anna Pietruczuk

Anna Pietruczuk starszy konsultant
d/s serwisu /
wdrożeń, SIMPLE S.A.

Temat: Dynamiczna tabela

Monika M.:
Tak z ciekawości, czemu rozwiazaniem ma być tabela tymczasowa a nie SELECT czy view z PIVOTEM ?
m

Bo po insercie wykonuje się na niej szereg operacji wyliczeniowych i dopiero wynik idzie do dalszej obróbki.
Link do wypowiedziLink
Marcin Mackiewicz

Marcin Mackiewicz Programista JAVA, RS
Adware Polska

Temat: Dynamiczna tabela

W PostgreSQL:
-- lista pracownikow

drop table if exists prac;

create table prac(

  id int,

  nazwa text

);



-- lista projektow

drop table if exists proc;

create table proc(

  id int,

  nazwa text

);



-- polaczenie projektow z pracownikami (przykladowe dane zadane w tym watku)

drop table if exists pro;

create table pro(

  pracownik text,

  projekt text,

  data date

);



-- Dodawanie testowych danych

insert into prac valueS (1, 'Pracownik1');

insert into prac values (2, 'Pracownik2');

insert into prac values (3, 'Pracownik3');



insert into proc values (1, 'Projekt1');

insert into proc values (2, 'Projekt2');

insert into proc values (3, 'Projekt3');



insert into pro VALUES ('Pracownik1', 'Projekt1', '2007-11-30');

insert into pro VALUES ('Pracownik2', 'Projekt1', '2007-11-30');

insert into pro VALUES ('Pracownik5', 'Projekt2', '2007-10-30');

insert into pro VALUES ('Pracownik3', 'Projekt3', '2007-10-30');

insert into pro VALUES ('Pracownik3', 'Projekt5', '2007-11-30');

insert into pro VALUES ('Pracownik1', 'Projekt4', '2007-10-30');



-- Wyciagniecie danych przynaleznosci pracownikow w danym czasie dla zdanej listy projektow

SELECT * FROM crosstab('

SELECT 

  p.nazwa as pracownik, 

  c.nazwa As projekt, 

  -- Zadaje ograniczenie: pracownik dodany do projektu w zadanym przedziale czasu (null = nie nalezy 0)

  CASE WHEN o.data BETWEEN ''2007-11-01'' AND ''2008-01-01'' THEN 1 ELSE 0 END as czlonek

FROM prac p

-- Dla potrzeb stworzenia wszystkich kombinacji pracownik-projekt niezaleznie od jego przynaleznosci

JOIN proc c ON 1 = 1

-- Dodanie informacji o przynaleznosci do konkretnego projektu - uzupelnienie macierzy

LEFT JOIN pro o ON (p.nazwa, c.nazwa) = (o.pracownik, o.projekt)

-- Ograniczenie do wybranych projektow

WHERE c.id in (1, 2)

', 'SELECT nazwa FROM proc WHERE id in (1,2)') AS ct(pracownik TEXT, "Projekt1" TEXT, "Projekt2" TEXT);
Funkcja crosstab z modułu tablefunc to taki pivot. Tabele zawierają id bo na liczbach się łatwiej pracuje niż na tekstach.

Idea jaką użyłem:
1. Weź pracowników, projekty i stwórz macierz wszystkich kombinacji
2. Macierz uzupełnij o daty przynależności z tabeli (pracownik, projekt, data).
3. Uzupełnioną macierz ogranicz to listy zadanych projektów
4. Wyniki z pkt 1-3 przestaw do postaci: pracownik, p1..pn

Aby sobie ułatwić na pewno stworzył bym sobie funkcje np. Przynaleznosc(int[] projekty) returning table, gdzie przekazywałbym listę projektów a w funkcji odpowiednio komponowałbym rezultat.

Tak na marginesie... Czy nie łatwiej byłoby pracować na płaskich danych a na interface użytkownika dopiero rysować w postaci tabeli przestawnej?
Link do wypowiedziLink
Marcin Mackiewicz

Marcin Mackiewicz Programista JAVA, RS
Adware Polska

Temat: Dynamiczna tabela

:) Chyba za bardzo zmęczony jestem. Któryś raz się łapie na posty sprzed 10 lat :)
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

[ASP.NET]dynamiczna liczba ...




Wyślij zaproszenie do
Anuluj