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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 problems with crosstab

Author  Topic 

damegu
Starting Member

5 Posts

Posted - 2014-08-31 : 06:20:25
Hello.
I have two tables and I would like to join them, then denormalize columns type and value. I am using postgresql.
tableA:
ID name
1 xxx
2 yyy
3 zzz
tableB:
IDB type value
1 weight 50
1 age 15
2 weight 80

Joining tables is pretty easy:

SELECT type,value FROM
tableA INNER JOIN tableB ON tableA.ID = tableB.IDB

But this query which should denormalize joined tables is not working, because I cannot use crosstab function. I tried CREATE EXTENSION tablefunc; but I am not a superuser.
Is there a way to denormalize table by other functions?

SELECT * FROM crosstab('
SELECT * FROM joinedTables'
) AS (
name varchar, "weight" real, "age" real
);

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-31 : 08:21:17
please post the result you are getting and the result you want
Go to Top of Page

damegu
Starting Member

5 Posts

Posted - 2014-08-31 : 09:02:13
ID name
1 xxx
2 yyy
3 zzz

IDB type value
1 weight 50
1 age 15
2 weight 80

From these two tables I want this result:

ID name weight age
1 xxx 50 15
2 yyy 80
3 zzz

I dont have any result because I cant use crosstab function so I need to figure out other way to do this.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-31 : 17:23:33
How about:
select a.ID
,max(case when b.type='weight' then b.value else null end) as weight
,max(case when b.type='age' then b.value else null end) as age
from tableA as a
left outer join tableB as b
on b.IDB=a.ID
group by a.ID
Go to Top of Page
   

- Advertisement -