Postgresql to pivot
up vote
1
down vote
favorite
I have a requirement like below in postgresql.
ID URL
1 www.google.com
1 www.yahoo.com
1 www.hotmail.com
I want the output to be like
ID URL1 URL2 URL3
1 www.google.com www.yahoo.com www.hotmail.com
I tried crosstab and I am getting an error SQL Error [42883]: ERROR: function crosstab(unknown) does not exist in Metabase and dbeaver. Can someone help with a SQL.
select id, case when rn = 1 then url end url1 ,
case when rn = 2 then url end url2
from (
SELECT *, row_number() over (partition by id) rn
FROM table1
where url is not null)start1
but its not as expected.
postgresql crosstab
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
asked Nov 8 at 10:08
George
112
add a comment |
up vote
1
down vote
favorite
I have a requirement like below in postgresql.
ID URL
1 www.google.com
1 www.yahoo.com
1 www.hotmail.com
I want the output to be like
ID URL1 URL2 URL3
1 www.google.com www.yahoo.com www.hotmail.com
I tried crosstab and I am getting an error SQL Error [42883]: ERROR: function crosstab(unknown) does not exist in Metabase and dbeaver. Can someone help with a SQL.
select id, case when rn = 1 then url end url1 ,
case when rn = 2 then url end url2
from (
SELECT *, row_number() over (partition by id) rn
FROM table1
where url is not null)start1
but its not as expected.
postgresql crosstab
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
asked Nov 8 at 10:08
George
112
2
You need to install the tablefunc extension
– a_horse_with_no_name
Nov 8 at 10:25
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a requirement like below in postgresql.
ID URL
1 www.google.com
1 www.yahoo.com
1 www.hotmail.com
I want the output to be like
ID URL1 URL2 URL3
1 www.google.com www.yahoo.com www.hotmail.com
I tried crosstab and I am getting an error SQL Error [42883]: ERROR: function crosstab(unknown) does not exist in Metabase and dbeaver. Can someone help with a SQL.
select id, case when rn = 1 then url end url1 ,
case when rn = 2 then url end url2
from (
SELECT *, row_number() over (partition by id) rn
FROM table1
where url is not null)start1
but its not as expected.
postgresql crosstab
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
asked Nov 8 at 10:08
George
112
I have a requirement like below in postgresql.
ID URL
1 www.google.com
1 www.yahoo.com
1 www.hotmail.com
I want the output to be like
ID URL1 URL2 URL3
1 www.google.com www.yahoo.com www.hotmail.com
I tried crosstab and I am getting an error SQL Error [42883]: ERROR: function crosstab(unknown) does not exist in Metabase and dbeaver. Can someone help with a SQL.
select id, case when rn = 1 then url end url1 ,
case when rn = 2 then url end url2
from (
SELECT *, row_number() over (partition by id) rn
FROM table1
where url is not null)start1
but its not as expected.
postgresql crosstab
postgresql crosstab
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
asked Nov 8 at 10:08
George
112
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
asked Nov 8 at 10:08
George
112
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
edited Nov 8 at 10:24
a_horse_with_no_name
285k45427525
285k45427525
asked Nov 8 at 10:08
George
112
asked Nov 8 at 10:08
George
112
asked Nov 8 at 10:08
George
112
112
2
You need to install the tablefunc extension
– a_horse_with_no_name
Nov 8 at 10:25
add a comment |
2
You need to install the tablefunc extension
– a_horse_with_no_name
Nov 8 at 10:25
2
2
You need to install the tablefunc extension
– a_horse_with_no_name
Nov 8 at 10:25
You need to install the tablefunc extension
– a_horse_with_no_name
Nov 8 at 10:25
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53205491%2fpostgresql-to-pivot%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
You need to install the tablefunc extension
– a_horse_with_no_name
Nov 8 at 10:25