SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query for making a report - Row to column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jacmarpet
Starting Member

1 Posts

Posted - 04/25/2013 :  07:45:26  Show Profile  Reply with Quote
Hello,

I am attempting to pull out data from a PostgreSQL database(standard SQL syntax) and use this data to create a report of user data from a system.

It is important that I get unique rows of each user, since having more than one will result in the report containing multiple records of the same person.

Here are the tables that I work with:

Table1:



Table2:



Table3:



The identity_id(Table1) equals the cat_item_id(Table2).
The attribute_id(Table2) equals attribute_id(Table3).

What I want as output, is a row per identity_id, that looks like this:

-----identity_id-----first_name----last_name----XATTRIB-CPR----XATTRIBEmail----XATTRIBPrimaryEmpaffi----AndTheRest---
--1--1GF7DKL32LH-------James--------Johnson-----54353453543----test@test.com---SomethingSomething-------valuevalue---
---------------------------------------------------------------------------------------------------------------------

So it sort of converts all the rows in Table 2, into 1 row, and at the same time, pulls the display_value of each of them, from Table3, and uses it as the column header, while putting in the value for that specific attribute(from Table2). I am able to get a table with all the data, but with a record for each attribute_id, which I can't use unfortunately. It would become a very spammy report.

Anyone able to crack this one, cause my brain can't!

Jacob.

Edited by - jacmarpet on 04/25/2013 07:57:22

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/25/2013 :  07:53:02  Show Profile  Reply with Quote
In SQL Server, we can use PIVOT for this kind of queries
I think you need query for PostgreSQL.. Its better to post in specific forums of PostgreSQL....
There might be syntactical differences between SQL Server and PostgreSQL

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/25/2013 :  08:06:20  Show Profile  Reply with Quote
In SQL Server, the solution looks like this...
SELECT *
FROM (SELECT t1.*, t3.*
		FROM Table1 t1 
		JOIN Table2 t2 ON t1.identity_id = t2.cat_item_id
		JOIN Table3 t3 ON t3.attribute_id = t2.attribute_id
	) p
PIVOT (SELECT MAX(display_value) FOR attribute_name IN ([XATTRIB-CPR], [XATTRIBEmail], [XATTRIBPrimaryEmpaffiliation], [XATTRIBWFInitiator] ))pvt


EDIT: This link may help you for PostgrSQL query
http://www.postgresonline.com/journal/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html
--
Chandu

Edited by - bandi on 04/25/2013 08:09:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000