Обсуждение: Doubt about query
HI everyone.
I have a table with X columns. And I want to make a select of 5 of the X columns in a way that i get all the 5 columns values only in one column (like i call everyone "AS bla" and as result, bla is an array of that columns), but if i use 5 selects with UNION ALL i just get [{bla => 3}, {bla => 3}, {bla => 1}, {bla => 5}, {bla => 2} ...] even with a GROUP BY. And besides that, using "as bla" is turning my results (3, 1, 5, 2) into strings.
What is the best way to collect the value of multiple integer columns as one? :|
Thanks.
HI everyone.I have a table with X columns. And I want to make a select of 5 of the X columns in a way that i get all the 5 columns values only in one column (like i call everyone "AS bla" and as result, bla is an array of that columns), but if i use 5 selects with UNION ALL i just get [{bla => 3}, {bla => 3}, {bla => 1}, {bla => 5}, {bla => 2} ...] even with a GROUP BY. And besides that, using "as bla" is turning my results (3, 1, 5, 2) into strings.What is the best way to collect the value of multiple integer columns as one? :|
Do you have a particular output column type you'd like the data to be in?
SELECT ARRAY[col1, col2, col3, col4, col5]::integer[] FROM tbl
David J.
Thanks for answering. I've already tryied that. When I do what you've said, i get this:
[{"array" => "{1,2,3,4,5}"}, {"array" => "6,7,8,9,10}"}...]
When what i want is this: [1,2,3,4,5,6,7,8,9,19]
:/
On Fri, Jun 5, 2015 at 2:40 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
HI everyone.I have a table with X columns. And I want to make a select of 5 of the X columns in a way that i get all the 5 columns values only in one column (like i call everyone "AS bla" and as result, bla is an array of that columns), but if i use 5 selects with UNION ALL i just get [{bla => 3}, {bla => 3}, {bla => 1}, {bla => 5}, {bla => 2} ...] even with a GROUP BY. And besides that, using "as bla" is turning my results (3, 1, 5, 2) into strings.What is the best way to collect the value of multiple integer columns as one? :|Do you have a particular output column type you'd like the data to be in?SELECT ARRAY[col1, col2, col3, col4, col5]::integer[] FROM tblDavid J.
Bianca Stephani.
"Killing time before time kill us"
"Panic called you out and took you in, giving you an easy game and letting you win"
"Who's gonna love you when you reach the end?"
"A diferença entre a vida e a arte é que a arte é mais suportável"
"Smile like you mean it"
"I told you I was brave, but I've lied"
"If you don't do anything, nothing can happen"
"Killing time before time kill us"
"Panic called you out and took you in, giving you an easy game and letting you win"
"Who's gonna love you when you reach the end?"
"A diferença entre a vida e a arte é que a arte é mais suportável"
"Smile like you mean it"
"I told you I was brave, but I've lied"
"If you don't do anything, nothing can happen"
Thanks for answering. I've already tryied that. When I do what you've said, i get this:[{"array" => "{1,2,3,4,5}"}, {"array" => "6,7,8,9,10}"}...]When what i want is this: [1,2,3,4,5,6,7,8,9,19]
Please don't top-post...
Nowhere are you mentioning JSON but I presume what you are trying to do involves that...
I'm not sure how you expect to the 10 numbers when you only want to concatenate 5 columns...
You should share what you have done and the sample data you are using.
SELECT array_agg(unnest) AS row_agg FROM (
SELECT unnest(col_agg) FROM (
SELECT ARRAY[...] AS col_agg FROM ...
)));
That gets you a single array with all the rows and columns in the same dimension.
David J.
I'm sorry. What is "top-post"? :/
What I want exactly is to get all the values from rows from all 5 coluns into only one row and one column.On Fri, Jun 5, 2015 at 3:38 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Thanks for answering. I've already tryied that. When I do what you've said, i get this:[{"array" => "{1,2,3,4,5}"}, {"array" => "6,7,8,9,10}"}...]When what i want is this: [1,2,3,4,5,6,7,8,9,19]Please don't top-post...Nowhere are you mentioning JSON but I presume what you are trying to do involves that...I'm not sure how you expect to the 10 numbers when you only want to concatenate 5 columns...You should share what you have done and the sample data you are using.SELECT array_agg(unnest) AS row_agg FROM (SELECT unnest(col_agg) FROM (SELECT ARRAY[...] AS col_agg FROM ...)));That gets you a single array with all the rows and columns in the same dimension.David J.
--
Bianca Stephani.
"Killing time before time kill us"
"Panic called you out and took you in, giving you an easy game and letting you win"
"Who's gonna love you when you reach the end?"
"A diferença entre a vida e a arte é que a arte é mais suportável"
"Smile like you mean it"
"I told you I was brave, but I've lied"
"If you don't do anything, nothing can happen"
"Killing time before time kill us"
"Panic called you out and took you in, giving you an easy game and letting you win"
"Who's gonna love you when you reach the end?"
"A diferença entre a vida e a arte é que a arte é mais suportável"
"Smile like you mean it"
"I told you I was brave, but I've lied"
"If you don't do anything, nothing can happen"
I'm sorry. What is "top-post"? :/
When you response is above the previous message as opposed to following it like mine do.
What you suggested to me as sql, give me a string of integers "1,2,3,4,5,6" and not an arrayA normal select of columns A, B, C, D would get 5 rows each one with 5 columns with 1 integer each rowXcolumn, for example. I want as result (as integer, not string) a single column and single row with a list of integers. Is that possible?What I want exactly is to get all the values from rows from all 5 coluns into only one row and one column.
No, it doesn't. If you want more help from me you will need to supply a self-contained example. The two-subquery+main version I hinted at below should give you an array in a single-row/single-column result. Try it and show the failing code if it doesn't. I'll admit I haven't tested it, and it might not be the best form or most efficient, but it should be workable.
David J.
On Fri, Jun 5, 2015 at 3:38 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:Thanks for answering. I've already tryied that. When I do what you've said, i get this:[{"array" => "{1,2,3,4,5}"}, {"array" => "6,7,8,9,10}"}...]When what i want is this: [1,2,3,4,5,6,7,8,9,19]Please don't top-post...Nowhere are you mentioning JSON but I presume what you are trying to do involves that...I'm not sure how you expect to the 10 numbers when you only want to concatenate 5 columns...You should share what you have done and the sample data you are using.SELECT array_agg(unnest) AS row_agg FROM (SELECT unnest(col_agg) FROM (SELECT ARRAY[...] AS col_agg FROM ...)));That gets you a single array with all the rows and columns in the same dimension.David J.
On 08/06/15 10:51, Bianca Stephani wrote:
> I'm sorry. What is "top-post"? :/
>
> What I want exactly is to get all the values from rows from all 5
> coluns into only one row and one column.
>
> A normal select of columns A, B, C, D would get 5 rows each one with 5
> columns with 1 integer each rowXcolumn, for example. I want as result
> (*as integer, not string*) a single column and single row with a list
> of integers. Is that possible?
>
> What you suggested to me as sql, give me a string of integers
> "1,2,3,4,5,6" and not an array
>
> On Fri, Jun 5, 2015 at 3:38 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Fri, Jun 5, 2015 at 2:00 PM, Bianca Stephani
> <bianca.stephani@gmail.com <mailto:bianca.stephani@gmail.com>>wrote:
>
> Thanks for answering. I've already tryied that. When I do what
> you've said, i get this:
>
> [{"array" => "{1,2,3,4,5}"}, {"array" => "6,7,8,9,10}"}...]
>
> When what i want is this: [1,2,3,4,5,6,7,8,9,19]
>
>
> Please don't top-post...
>
> Nowhere are you mentioning JSON but I presume what you are trying
> to do involves that...
>
> I'm not sure how you expect to the 10 numbers when you only want
> to concatenate 5 columns...
>
> You should share what you have done and the sample data you are using.
>
> SELECT array_agg(unnest) AS row_agg FROM (
> SELECT unnest(col_agg) FROM (
> SELECT ARRAY[...] AS col_agg FROM ...
> )));
>
> That gets you a single array with all the rows and columns in
> the same dimension.
>
> David J.
>
>
>
>
>
> --
> Bianca Stephani.
>
> /"Killing time before time kill us"
> "Panic called you out and took you in, giving you an easy game and
> letting you win"
> "Who's gonna love you when you reach the end?"
> "A diferença entre a vida e a arte é que a arte é mais suportável"
> "Smile like you mean it"
> "I told you I was brave, but I've lied"
> "If you don't do anything, nothing can happen"/
Top posting is reply here! And not at the the end of the post.
You are encouraged to intersperse comments in the original post, if the
context is important
On 08/06/15 10:51, Bianca Stephani wrote:
> I'm sorry. What is "top-post"? :/
Explained above! (An interspersed comment, in this case a bit silly!)
>
> What I want exactly is to get all the values from rows from all 5
> coluns into only one row and one column.
>
> A normal select of columns A, B, C, D would get 5 rows each one with 5
> columns with 1 integer each rowXcolumn, for example. I want as result
> (*as integer, not string*) a single column and single row with a list
> of integers. Is that possible?
>
> What you suggested to me as sql, give me a string of integers
> "1,2,3,4,5,6" and not an array
>
> On Fri, Jun 5, 2015 at 3:38 PM, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Fri, Jun 5, 2015 at 2:00 PM, Bianca Stephani
> <bianca.stephani@gmail.com <mailto:bianca.stephani@gmail.com>>wrote:
>
> Thanks for answering. I've already tryied that. When I do what
> you've said, i get this:
>
> [{"array" => "{1,2,3,4,5}"}, {"array" => "6,7,8,9,10}"}...]
>
> When what i want is this: [1,2,3,4,5,6,7,8,9,19]
>
>
> Please don't top-post...
>
> Nowhere are you mentioning JSON but I presume what you are trying
> to do involves that...
>
> I'm not sure how you expect to the 10 numbers when you only want
> to concatenate 5 columns...
>
> You should share what you have done and the sample data you are using.
>
> SELECT array_agg(unnest) AS row_agg FROM (
> SELECT unnest(col_agg) FROM (
> SELECT ARRAY[...] AS col_agg FROM ...
> )));
>
> That gets you a single array with all the rows and columns in
> the same dimension.
>
> David J.
>
>
>
[...]
This is were you should normally reply.
To omit large parts of a previous post you can use '[...]'.
This is a convention I introduced into usenet in the early 1990's!!!
Previously people used '[ omitted ]'
Cheers,
Gavin
Hi Bianca,
What are you looking for, I think it is the "string_agg" function.
So for example the following query:
"Select string_agg(name, ', ') from table;"
will return "name1, name2, ....".
If you need top 5 rows you can add "limit 5" or other conditions you need.
Hope it helps.
On 05.06.2015, at 20:25, Bianca Stephani <bianca.stephani@gmail.com> wrote:HI everyone.I have a table with X columns. And I want to make a select of 5 of the X columns in a way that i get all the 5 columns values only in one column (like i call everyone "AS bla" and as result, bla is an array of that columns), but if i use 5 selects with UNION ALL i just get [{bla => 3}, {bla => 3}, {bla => 1}, {bla => 5}, {bla => 2} ...] even with a GROUP BY. And besides that, using "as bla" is turning my results (3, 1, 5, 2) into strings.What is the best way to collect the value of multiple integer columns as one? :|Thanks.
On Mon, Jun 8, 2015 at 4:38 AM, Christoffer Westring <dille.westring@gmail.com> wrote:
Hi Bianca,What are you looking for, I think it is the "string_agg" function.So for example the following query:"Select string_agg(name, ', ') from table;"will return "name1, name2, ....".If you need top 5 rows you can add "limit 5" or other conditions you need.
Thanks, but i don't want the top 5 records. I maybe have not been clear about what I want...
I have a table with N columns. Of this N, i want the value of 5 columns. So, if I do a normal select, i would get something like:
column1 column2 column3 column4 column5
value1row1 value2row1 value3row1 value4row1 value5row1
value1row2 value2row2 value3row2 value4row2 value5row2
value1row3 value2row3 value3row3 value4row3 value5row3
And what i really want is:
column
[value1row1,value2row1, value3row1, value4row1, value5row1, value1row2 , value2row2, value3row2, value4row2, value5row2, value1row3, value2row3, value3row3, value4row3, value5row3]
Can I do that?
Hope it helps.
On 05.06.2015, at 20:25, Bianca Stephani <bianca.stephani@gmail.com> wrote:HI everyone.I have a table with X columns. And I want to make a select of 5 of the X columns in a way that i get all the 5 columns values only in one column (like i call everyone "AS bla" and as result, bla is an array of that columns), but if i use 5 selects with UNION ALL i just get [{bla => 3}, {bla => 3}, {bla => 1}, {bla => 5}, {bla => 2} ...] even with a GROUP BY. And besides that, using "as bla" is turning my results (3, 1, 5, 2) into strings.What is the best way to collect the value of multiple integer columns as one? :|Thanks.
Bianca Stephani.
"Killing time before time kill us"
"Panic called you out and took you in, giving you an easy game and letting you win"
"Who's gonna love you when you reach the end?"
"A diferença entre a vida e a arte é que a arte é mais suportável"
"Smile like you mean it"
"I told you I was brave, but I've lied"
"If you don't do anything, nothing can happen"
"Killing time before time kill us"
"Panic called you out and took you in, giving you an easy game and letting you win"
"Who's gonna love you when you reach the end?"
"A diferença entre a vida e a arte é que a arte é mais suportável"
"Smile like you mean it"
"I told you I was brave, but I've lied"
"If you don't do anything, nothing can happen"