Solved

Query to pivot data

Posted on 2014-02-22
5
334 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 41

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 41

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 31

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 41

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now