<?xml version="1.0" encoding="utf-8"?>
<feed xmlns="http://www.w3.org/2005/Atom">
	<title type="html"><![CDATA[Forum PostgreSQL - Dynamiczna zamiana wierszy na kolumny]]></title>
	<link rel="self" href="http://forum.postgresql.org.pl/extern.php?action=feed&amp;tid=1237&amp;type=atom"/>
	<updated>2012-02-18T14:39:56Z</updated>
	<generator>PunBB</generator>
	<id>https://forum.postgresql.org.pl/viewtopic.php?id=1237</id>
		<entry>
			<title type="html"><![CDATA[Odp: Dynamiczna zamiana wierszy na kolumny]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=3457#p3457"/>
			<content type="html"><![CDATA[Możesz użyć funkcji crosstab (żeby była dostępna należy dołączyć do bazy moduł [url=http://www.postgresql.org/docs/current/static/tablefunc.html]tablefunc[/url]) np.

[code]Select * From crosstab('Select dv_user_id, dt_name, dv_value From data_value Join data_type On (dt_id = dv_type_id)') As ct(dv_user_id int, "Date_Type_1" int, "Data_Type_2" int);
 dv_user_id | Date_Type_1 | Data_Type_2 
------------+-------------+-------------
          1 |           6 |           7
          2 |           8 |           9
(2 rows)[/code]

Sęk polega jednak na tym, że crosstab zwraca setof record i trzeba mu pomóc podając explicite kolumny. W zasadzie to join nawet nie jest potrzebny i można to uprościć do zapytania:

[code]Select * From crosstab('Select dv_user_id, dv_type_id, dv_value From data_value Order By dv_user_id, dv_type_id') As ct(dv_user_id int, "Date_Type_1" int, "Data_Type_2" int);
 dv_user_id | Date_Type_1 | Data_Type_2 
------------+-------------+-------------
          1 |           6 |           7
          2 |           8 |           9
(2 rows)[/code]

Oczywiście z takiego zapytania można zrobić widok przez zwykłe Create View nazwa_widoku As. Jeżeli jednak dane w tabeli data_type mają się zmieniać i konieczne jest zautomatyzowanie zapytania, to nie widzę innego sposobu jak napisanie funkcji w PL/pgSQL, której zadaniem byłoby ew. zdropodowanie widoku (DROP VIEW IF EXISTS) oraz jego utworzenie przez dynamiczny SQL, tzn. wyciągnąć przez select aktualną listę dt_name i wstawić ją po obrobieniu do sekcji As dla crosstab. Dodatkowo możliwe byłoby utworzenie triggera w taki sposób, aby zmiana w tabeli data_type powodowała automatyczne odświeżenie widoku. Jest z tym trochę zachodu, ale nie widzę na chwilę obecną lepszego rozwiązania.

Kod dla wspomnianej funkcji:

[code]Drop Function If Exists handle_data_view();

Create Function handle_data_view() Returns void As $BODY$
Declare
    query text;
    data_type_row record;
Begin
    Drop View If Exists data_view; -- step one: remove view if neccessary

    -- step two: construct crosstab query dynamically
    query := 'Select * From crosstab(''Select dv_user_id, dv_type_id, dv_value From data_value Order By dv_user_id, dv_type_id'') As ct(dv_user_id int';
    For data_type_row In Select dt_name From data_type Order By dt_id Loop
        query := query || ', "' || data_type_row.dt_name || '" int';
    End Loop;
    query := query || ')';

    -- step three: create view using that query
    Execute 'Create View data_view As ' || query;
End;
$BODY$ Language plpgsql;[/code]

Wywołanie i wynik:

[code]Select handle_data_view();
 handle_data_view 
------------------
 
(1 row)
Select * From data_view;
 dv_user_id | Date_Type_1 | Data_Type_2 
------------+-------------+-------------
          1 |           6 |           7
          2 |           8 |           9
(2 rows)[/code]]]></content>
			<author>
				<name><![CDATA[gszpetkowski]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1223</uri>
			</author>
			<updated>2012-02-18T14:39:56Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=3457#p3457</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Dynamiczna zamiana wierszy na kolumny]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=3456#p3456"/>
			<content type="html"><![CDATA[Witam!

Mam dwie tabele:

data_type
-------------
dt_id int,
dt_name string

data_value
--------------
dv_id int
dv_user_id int
dv_value int
dv_type_id int   (Referencja do data_type.dt_id)

W tabelach mam dane:

dt_id | dt_name
--------------------
  1    | Data_Type_1
  2    | Data_Type_2

dv_id | dv_user_id | dv_value | dv_type_id
------------------------------------------------------
    1   |          1       |       6        |       1
    2   |          1       |       7        |       2
    3   |          2       |       8        |       1
    4   |          2       |       9        |       2

A w wyniku zapytania chciałbym uzyskać tabelę:

dv_user_id | Data_Type_1 | Data_Type_1
-----------------------------------------------------
        1         |            6         |         7
        2         |            8         |         9


Dodatkowym utrudnieniem jest to, że wszystkie dane zmieniają się dynamicznie, także nie mogę napisać stałego zapytania.
Najlepiej gdyby udało się wyświetlać docelową tabelę w postaci widoku.

Jak ktoś ma jakiś pomysł to proszę o podzielenie się nim, gdyż ja dostałem totalnego zaciemnienia :/]]></content>
			<author>
				<name><![CDATA[neon]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1370</uri>
			</author>
			<updated>2012-02-18T13:05:41Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=3456#p3456</id>
		</entry>
</feed>
