Transpose a result with an unknown number of rows/columns
up vote
0
down vote
favorite
Let's say I have a query that yields these results:
2015-2016 | 2016-2017 | 2017-2018
-----------------------------------------------
Alpha 2 5 9
Bravo 7 14 21
Charlie 1 2 4
But I want the results to be transposed like this:
Alpha | Bravo | Charlie
-----------------------------------------------
2015-2016 2 7 1
2016-2017 5 14 2
2017-2018 9 21 4
AND... my original results will have an arbitrary number of rows (to be turned to columns), which I cannot know in advance. For example, Echo and Foxtrot might appear some day but I won't know their names in advance.
Is there a way to simply transpose / pivot this table, without knowing the contents or length of the table in advance?
I've seen similar questions, where the answer ended up being to use unpivot or crosstab, but they all seem to require a knowledge of the output structure in advance.
postgresql pivot transpose
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
add a comment |
up vote
0
down vote
favorite
Let's say I have a query that yields these results:
2015-2016 | 2016-2017 | 2017-2018
-----------------------------------------------
Alpha 2 5 9
Bravo 7 14 21
Charlie 1 2 4
But I want the results to be transposed like this:
Alpha | Bravo | Charlie
-----------------------------------------------
2015-2016 2 7 1
2016-2017 5 14 2
2017-2018 9 21 4
AND... my original results will have an arbitrary number of rows (to be turned to columns), which I cannot know in advance. For example, Echo and Foxtrot might appear some day but I won't know their names in advance.
Is there a way to simply transpose / pivot this table, without knowing the contents or length of the table in advance?
I've seen similar questions, where the answer ended up being to use unpivot or crosstab, but they all seem to require a knowledge of the output structure in advance.
postgresql pivot transpose
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
Not possible. The database needs to know the number of columns before actually running the query. This is a limitation of the SQL language. It's not specific to Postgres
– a_horse_with_no_name
Nov 19 at 21:18
@a_horse_with_no_name, it seems like you're right, so go ahead and submit your comment as an answer, which I'll accept.
– Greg Matthew Crossley
Nov 20 at 14:12
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Let's say I have a query that yields these results:
2015-2016 | 2016-2017 | 2017-2018
-----------------------------------------------
Alpha 2 5 9
Bravo 7 14 21
Charlie 1 2 4
But I want the results to be transposed like this:
Alpha | Bravo | Charlie
-----------------------------------------------
2015-2016 2 7 1
2016-2017 5 14 2
2017-2018 9 21 4
AND... my original results will have an arbitrary number of rows (to be turned to columns), which I cannot know in advance. For example, Echo and Foxtrot might appear some day but I won't know their names in advance.
Is there a way to simply transpose / pivot this table, without knowing the contents or length of the table in advance?
I've seen similar questions, where the answer ended up being to use unpivot or crosstab, but they all seem to require a knowledge of the output structure in advance.
postgresql pivot transpose
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
Let's say I have a query that yields these results:
2015-2016 | 2016-2017 | 2017-2018
-----------------------------------------------
Alpha 2 5 9
Bravo 7 14 21
Charlie 1 2 4
But I want the results to be transposed like this:
Alpha | Bravo | Charlie
-----------------------------------------------
2015-2016 2 7 1
2016-2017 5 14 2
2017-2018 9 21 4
AND... my original results will have an arbitrary number of rows (to be turned to columns), which I cannot know in advance. For example, Echo and Foxtrot might appear some day but I won't know their names in advance.
Is there a way to simply transpose / pivot this table, without knowing the contents or length of the table in advance?
I've seen similar questions, where the answer ended up being to use unpivot or crosstab, but they all seem to require a knowledge of the output structure in advance.
postgresql pivot transpose
postgresql pivot transpose
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
asked Nov 19 at 21:14
Greg Matthew Crossley
325314
325314
Not possible. The database needs to know the number of columns before actually running the query. This is a limitation of the SQL language. It's not specific to Postgres
– a_horse_with_no_name
Nov 19 at 21:18
@a_horse_with_no_name, it seems like you're right, so go ahead and submit your comment as an answer, which I'll accept.
– Greg Matthew Crossley
Nov 20 at 14:12
add a comment |
Not possible. The database needs to know the number of columns before actually running the query. This is a limitation of the SQL language. It's not specific to Postgres
– a_horse_with_no_name
Nov 19 at 21:18
@a_horse_with_no_name, it seems like you're right, so go ahead and submit your comment as an answer, which I'll accept.
– Greg Matthew Crossley
Nov 20 at 14:12
Not possible. The database needs to know the number of columns before actually running the query. This is a limitation of the SQL language. It's not specific to Postgres
– a_horse_with_no_name
Nov 19 at 21:18
Not possible. The database needs to know the number of columns before actually running the query. This is a limitation of the SQL language. It's not specific to Postgres
– a_horse_with_no_name
Nov 19 at 21:18
@a_horse_with_no_name, it seems like you're right, so go ahead and submit your comment as an answer, which I'll accept.
– Greg Matthew Crossley
Nov 20 at 14:12
@a_horse_with_no_name, it seems like you're right, so go ahead and submit your comment as an answer, which I'll accept.
– Greg Matthew Crossley
Nov 20 at 14:12
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f53382725%2ftranspose-a-result-with-an-unknown-number-of-rows-columns%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
Not possible. The database needs to know the number of columns before actually running the query. This is a limitation of the SQL language. It's not specific to Postgres
– a_horse_with_no_name
Nov 19 at 21:18
@a_horse_with_no_name, it seems like you're right, so go ahead and submit your comment as an answer, which I'll accept.
– Greg Matthew Crossley
Nov 20 at 14:12