problems with crosstab - SQL Server Forums

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources