Обсуждение: Select row cells as new columns

Hello. I hope you can help me with this or at least guide me into the right
direction:

I have 2 tables:

CREATE TABLE infos
( id integer NOT NULL DEFAULT nextval('info_id_seq'::regclass), name text NOT NULL, id_member integer NOT NULL, title
text,min_length integer NOT NULL DEFAULT 0, max_length integer NOT NULL DEFAULT 30, required boolean NOT NULL DEFAULT
false,type text NOT NULL DEFAULT 'text'::text, CONSTRAINT info_pkey PRIMARY KEY (id ), CONSTRAINT infos_id_member_fkey
FOREIGNKEY (id_member)     REFERENCES members (id) MATCH SIMPLE     ON UPDATE NO ACTION ON DELETE NO ACTION
 
)
WITH ( OIDS=FALSE
);
ALTER TABLE infos OWNER TO postgres;

-- Index: info_id_idx

-- DROP INDEX info_id_idx;

CREATE INDEX info_id_idx ON infos USING btree (id );

and 

CREATE TABLE info_data
( id serial NOT NULL, id_info integer, value text, CONSTRAINT info_data_pkey PRIMARY KEY (id ), CONSTRAINT
info_data_id_info_fkeyFOREIGN KEY (id_info)     REFERENCES infos (id) MATCH SIMPLE     ON UPDATE NO ACTION ON DELETE NO
ACTION
)
WITH ( OIDS=FALSE
);
ALTER TABLE info_data OWNER TO postgres;

-- Index: info_data_id_idx

-- DROP INDEX info_data_id_idx;

CREATE INDEX info_data_id_idx ON info_data USING btree (id );

with the following values:

infos: 
COPY infos (id, name, id_member, title, min_length, max_length, required,
type) FROM stdin;
1    nume    1    Nume    0    30    t    text
2    prenume    1    Prenume    0    30    t    text
3    cnp    1    C.N.P.    13    13    t    number
4    nume anterior    1    Nume anterior    0    30    f    text
5    stare civila    1    Starea civila    0    30    f    text
6    cetatenie    1    Cetatenie    0    30    f    text
7    rezidenta    1    Rezidenta    0    30    f    text
9    tip act    1    C.I. / B.I.    0    10    t    text
10    serie ci    1    Serie C.I. / B.I.    0    30    t    text
11    numar ci    1    Numar C.I. / B.I.    0    30    t    text
12    data eliberarii    1    Data eliberarii    0    30    t    text
13    eliberat de    1    Eliberat de    0    30    t    text
8    adresa    1    Adresa    0    50    f    text
\.

info_data:
COPY info_data (id, id_info, value) FROM stdin;
1    1    a
2    2    a
3    3    100
4    4    
5    5    
6    6    
7    7    
8    8    
9    9    ci
10    10    sv
11    11    13
12    12    132
13    13    123
14    1    b
15    2    b
16    3    100
17    4    
18    5    
19    6    
20    7    
21    8    
22    9    BI
23    10    XT
24    11    123
25    12    10
26    13    10
\. 

The question:
How can I achive this output?

nume, prenume, cnp, nume anterior, stare civila, ... (as columns - built
from unique rows from infos)
a      , a, .......
b      , b, ....... (as rows)


http://postgresql.1045698.n5.nabble.com/file/n5709987/info_data.sql
info_data.sql 
http://postgresql.1045698.n5.nabble.com/file/n5709987/infos.sql infos.sql 


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Select-row-cells-as-new-columns-tp5709987.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On Fri, 25 May 2012 02:03:41 -0700 (PDT), danycxxx wrote:
[...]
> The question:
> How can I achive this output?
>
> nume, prenume, cnp, nume anterior, stare civila, ... (as columns - 
> built
> from unique rows from infos)
> a      , a, .......
> b      , b, ....... (as rows)

Did you look at crosstab functions?
http://www.postgresql.org/docs/9.1/static/tablefunc.html


Jan
-- 
professional: http://www.oscar-consult.de
private: http://neslonek.homeunix.org/drupal/

Yes, I've looked at it, but id doesn't create the desired output. After more
research I've found that I my design is similar to Entity, Attribute and
Value(EAV) design and I think I have to redesign. Any suggestion regarding
EAV? Is there any other approach?

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Select-row-cells-as-new-columns-tp5709987p5710005.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

danycxxx wrote:
> Yes, I've looked at it, but id doesn't create the desired output. After more
> research I've found that I my design is similar to Entity, Attribute and
> Value(EAV) design and I think I have to redesign. Any suggestion regarding
> EAV? Is there any other approach?

EAV is controversial. I am uncomfortable with it because it implements 
keys as values.

I suggest that you not use EAV. Its putative flexibility comes at a large 
implementation price.

The other approach is relational database design. You model an entity as 
a collection of tables, each of which represents an aspect of the data 
pertinent to the entity. Each table has columns, the labels of which 
correspond generally to the names of attributes for that aspect. Each 
row of each table provides values for its respective named columns.

So a table roughly models what I'll call an "entitylet" - a cohesive piece 
of the entity model.

The rules to decompose entity models into relational data models 
constitute "normalization". I suggest you create a relational data model 
normalized to at least third normal form.

-- 
Lew