pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect row cells as new columns
4+ messages / 3 participants
[nested] [flat]
* Select row cells as new columns
@ 2012-05-25 09:03 danycxxx <danycxxx@yahoo.com>
2012-05-25 12:41 ` Re: Select row cells as new columns Jan Lentfer <Jan.Lentfer@web.de>
0 siblings, 1 reply; 4+ messages in thread
From: danycxxx @ 2012-05-25 09:03 UTC (permalink / raw)
To: pgsql-sql
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 FOREIGN KEY (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_fkey FOREIGN 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select row cells as new columns
2012-05-25 09:03 Select row cells as new columns danycxxx <danycxxx@yahoo.com>
@ 2012-05-25 12:41 ` Jan Lentfer <Jan.Lentfer@web.de>
2012-05-25 12:57 ` Re: Select row cells as new columns danycxxx <danycxxx@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Lentfer @ 2012-05-25 12:41 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select row cells as new columns
2012-05-25 09:03 Select row cells as new columns danycxxx <danycxxx@yahoo.com>
2012-05-25 12:41 ` Re: Select row cells as new columns Jan Lentfer <Jan.Lentfer@web.de>
@ 2012-05-25 12:57 ` danycxxx <danycxxx@yahoo.com>
2012-05-31 16:55 ` Re: Select row cells as new columns lewbloch@gmail.com
0 siblings, 1 reply; 4+ messages in thread
From: danycxxx @ 2012-05-25 12:57 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select row cells as new columns
2012-05-25 09:03 Select row cells as new columns danycxxx <danycxxx@yahoo.com>
2012-05-25 12:41 ` Re: Select row cells as new columns Jan Lentfer <Jan.Lentfer@web.de>
2012-05-25 12:57 ` Re: Select row cells as new columns danycxxx <danycxxx@yahoo.com>
@ 2012-05-31 16:55 ` lewbloch@gmail.com
0 siblings, 0 replies; 4+ messages in thread
From: lewbloch@gmail.com @ 2012-05-31 16:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-05-31 16:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-25 09:03 Select row cells as new columns danycxxx <danycxxx@yahoo.com>
2012-05-25 12:41 ` Jan Lentfer <Jan.Lentfer@web.de>
2012-05-25 12:57 ` danycxxx <danycxxx@yahoo.com>
2012-05-31 16:55 ` lewbloch@gmail.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox