Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2242 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
2242 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  
 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.24 seconds. Powered By: Snitz Forums 2000