SQL Query Help (Postgresql)

Question

I'm having trouble wrapping my head around a query. I have the following 3 tables:

documents (
    id,
    title
);

positions (
    id,
    title
);

documents_positions (
    document_id,
    position_id
);

What I am trying to get (my requested outcome) is a matrix of documents, and what positions they apply to. So each row would have document title, and then have a column for every position and a column next to it with True or False if the positions applies to the document. I suspect some kind of LEFT JOIN is required, because on each row after document, I want to list every position from the positions table and whether or not the document applies to it. Make sense?

Can you provide input data and expected outcome data?

Bohemian
– Bohemian ♦

2011-07-28 10:22:24 +00:00
Commented Jul 28, 2011 at 10:22 — Bohemian
– Bohemian ♦, Commented Jul 28, 2011 at 10:22

Andomar · Accepted Answer · 2011-07-28 10:37:12Z

1

You could use a cross join to build the matrix, and then left join to find the positions in the matrix that are filled:

select  d.title
,       p.title
,       case when dp.document_id is null then 'hrmm' else 'HALLELUJAH' end
from    documents d
cross join
        positions p
left join
        documents_positions dp
on      dp.document_id = d.id
        and dp.position_id = p.id

answered Jul 28, 2011 at 10:37

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pyrite Over a year ago

This gives me the correct data, but how can I have the output be where each position is on the same row as the document?

Andomar Over a year ago

@Pyrite: That's called "pivoting". Excel can do that for you, or most other reporting tools. SQL doesn't like a variable number of columns, making pivoting next to impossible to do in pure SQL.

Pyrite Over a year ago

I guess I mean to say, how to PIVOT the results in Postgresql?

Pyrite Over a year ago

It seems to PIVOT in Postgresql is to manually define each position in the SQL query, but this is not an option, unless it can be done dynamically. So I think I should just implement the output in the client application (in my case, an HTML table using PHP). So the question remains, how to do that I guess.

Andomar Over a year ago

@Pyrite: order by p.id, d.id. Then add a new column for every row from the database, and a new row every time the position changes.

|

ain · Accepted Answer · 2011-07-28 11:09:24Z

1

Since you want to turn positions rows into columns you have to "pivot" them. In PostgreSQL this is done with crosstab function. However, crosstab seems to require that you define the number of output columns in the query which you can't do as the number of rows in the positions is subject to change? I'm not a PostgreSQL user myself so perhaps there is some trick to build the dynamic query I don't know of but it seems to be easier to use query like Andomar posted and then pivot the rows in your client code...

answered Jul 28, 2011 at 11:09

ain

22.8k3 gold badges60 silver badges76 bronze badges

Collectives™ on Stack Overflow

SQL Query Help (Postgresql)

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related