Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query to pivot data

Posted on 2014-02-22
5
343 Views
Last Modified: 2014-02-24
I have created a query that reads data from several tables and each record is contained on a single line.  I did this joining several queries and the result was a very long query (not time, but characters).  Please see below the query I used:

select *
from
(
	select id from aaa
) aaa
left join
(
	select aaa_id,bbb.id as bbb1_id,
	count(case when id is not null then 1 else null end) as bbb1_count
	from bbb
	where bbb.v=1
	group by aaa_id,id
) bbb1
on aaa.id=bbb1.aaa_id
left join
(
	select aaa_id,bbb.id as bbb2_id,
	count(case when id is not null then 1 else null end) as bbb2_count
	from bbb
	where bbb.v=2
	group by aaa_id,id
) bbb2
on aaa.id=bbb2.aaa_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc1_id,
	count(case when ccc.id is not null then 1 else null end) as ccc11_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=1
	  and ccc.v=1
	group by aaa_id,bbb_id,ccc.id
) ccc11
on aaa.id=bbb1.aaa_id and bbb1.bbb1_id=ccc11.bbb_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc2_id,
	count(case when ccc.id is not null then 1 else null end) as ccc12_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=1
	  and ccc.v=2
	group by aaa_id,bbb_id,ccc.id
) ccc12
on aaa.id=bbb1.aaa_id and bbb1.bbb1_id=ccc12.bbb_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc21_id,
	count(case when ccc.id is not null then 1 else null end) as ccc21_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=2
	  and ccc.v=1
	group by aaa_id,bbb_id,ccc.id
) ccc21
on aaa.id=bbb2.aaa_id and bbb2.bbb2_id=ccc21.bbb_id
left join
(
	select aaa_id,bbb_id,ccc.id as ccc22_id,
	count(case when ccc.id is not null then 1 else null end) as ccc22_count
	from bbb
	inner join ccc on bbb.id=ccc.bbb_id
	where bbb.v=2
	  and ccc.v=2
	group by aaa_id,bbb_id,ccc.id
) ccc22
on aaa.id=bbb2.aaa_id and bbb2.bbb2_id=ccc22.bbb_id

Open in new window


Obviously, this is a very long query and I would like to reduce it to a  simple, elegant query.  I've tried this:

select *
from aaa
left join bbb on aaa.id=bbb.aaa_id
left join ccc on bbb.id=ccc.bbb_id

Open in new window


But end up with multiple lines for each record (record would be aaa.id).  Is there a better way to accomplish the result from my first query but much shorter?  I'm using postgres 8.4 so I'm not sure what sort of pivot options are available but I definitely won't be able to perform mssql or mysql queries that have those specific functions.

I'm including some data to generate tables with data:
create table aaa (id integer);
create table bbb (id integer,aaa_id integer, v integer);
create table ccc (id integer,bbb_id integer, v integer);
insert into aaa(id) values(1);
insert into aaa(id) values(2);
insert into aaa(id) values(3);
insert into aaa(id) values(4);
insert into bbb(id,aaa_id,v) values(11,1,1);
insert into bbb(id,aaa_id,v) values(12,1,2);
insert into bbb(id,aaa_id,v) values(21,2,1);
insert into bbb(id,aaa_id,v) values(22,2,2);
insert into bbb(id,aaa_id,v) values(31,3,1);
insert into bbb(id,aaa_id,v) values(42,4,2);
insert into ccc(id,bbb_id,v) values(111,11,1);
insert into ccc(id,bbb_id,v) values(112,11,2);
insert into ccc(id,bbb_id,v) values(211,22,1);
insert into ccc(id,bbb_id,v) values(312,31,2);
insert into ccc(id,bbb_id,v) values(411,42,1);
insert into ccc(id,bbb_id,v) values(412,42,2);

Open in new window

0
Comment
Question by:mock5c
  • 3
5 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 39880705
You may try this:
SELECT aaa.id, 
       COUNT(case when bbb.v=1 THEN bbb.id ELSE null END) as bbb1_count, 
       COUNT(case when bbb.v=2 THEN bbb.id ELSE null END) as bbb2_count, 
       COUNT(case when bbb.v=1 and ccc.v=1 THEN ccc.id ELSE null END) as ccc11_count, 
       COUNT(case when bbb.v=1 and ccc.v=2 THEN ccc.id ELSE null END) as ccc12_count, 
       COUNT(case when bbb.v=2 and ccc.v=1 THEN ccc.id ELSE null END) as ccc21_count, 
       COUNT(case when bbb.v=2 and ccc.v=2 THEN ccc.id ELSE null END) as ccc22_count
  FROM aaa
  LEFT JOIN bbb ON aaa.id=bbb.aaa_id
  LEFT JOIN ccc ON bbb.id=ccc.bbb_id
 GROUP BY aaa.id

Open in new window

There is a difference between your and my query so we should explain why bbb1_count = 1 on the first result row and why bbb2_count = 1 on the last result row in your case.
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39880715
We have to count distinct values in the case on table bbb because several ccc rows related to one bbb row multiplies the result...
SELECT aaa.id, 
       COUNT(DISTINCT case when bbb.v=1 THEN bbb.id ELSE null END) as bbb1_count, 
       COUNT(DISTINCT case when bbb.v=2 THEN bbb.id ELSE null END) as bbb2_count, 
       COUNT(case when bbb.v=1 and ccc.v=1 THEN ccc.id ELSE null END) as ccc11_count, 
       COUNT(case when bbb.v=1 and ccc.v=2 THEN ccc.id ELSE null END) as ccc12_count, 
       COUNT(case when bbb.v=2 and ccc.v=1 THEN ccc.id ELSE null END) as ccc21_count, 
       COUNT(case when bbb.v=2 and ccc.v=2 THEN ccc.id ELSE null END) as ccc22_count
  FROM aaa
  LEFT JOIN bbb ON aaa.id=bbb.aaa_id
  LEFT JOIN ccc ON bbb.id=ccc.bbb_id
 GROUP BY aaa.id

Open in new window

It is better visible when you issue ungroupped query:
SELECT * FROM aaa
  LEFT JOIN bbb ON aaa.id=bbb.aaa_id
  LEFT JOIN ccc ON bbb.id=ccc.bbb_id

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39882997
To get one row for each aaa.id will require some type of aggregation of values from the bbb and ccc tables, something like the embedded snippet. Is that what you want?
aaa.id	bbb.id	bbb.aaa_id	bbb.v	ccc.id		ccc.bbb_id	ccc.v
1	11,12	1		1,2	111,112		11		1,2
2	21,22	2		1,2	211		22		1
3	31	3		1	312		31		2
4	42	4		2	411,412		42		1,2

Open in new window

0
 
LVL 42

Expert Comment

by:pcelba
ID: 39883082
@mock5c: Do you need IDs on output? I would guess no except aaa.id
0
 

Author Closing Comment

by:mock5c
ID: 39883123
This is exactly what I needed.  It's a condensed query that gives me the exact same result of the very long query I posted.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Read about achieving the basic levels of HRIS security in the workplace.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question