Query to pivot data

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

mock5cAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
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
 
pcelbaCommented:
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
 
awking00Commented:
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
 
pcelbaCommented:
@mock5c: Do you need IDs on output? I would guess no except aaa.id
0
 
mock5cAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.