Re: SQL: Return one row with multiple columns generated from a single column from multiple rows

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I'm confused as to what you want..

Do you want just the two columns, (Server,All_Pct_Used)?
In that case quit trying to copy and paste into Excel..

Instead, simply save the results from ACS as an Excel document.

Charles


On Mon, May 6, 2024 at 4:58 PM Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>
wrote:

> Two queries below with results.  The first is a straight file dump.  The
> second is showing one line per server, aggregating the Pct_Used column for
> the server.  (By the time I'm done, there will be 30 servers total.)  I
> need the Pct_Used values put into their own column because, when I run this
> in ACS, I can copy the results into a spreadsheet, and the multiple values
> will each be pasted into their own cell.  If I copy the results from the
> last query and paste them into my spreadsheet, the values get pasted into
> their own cells, but in a single column.  If the Pct_Used values in the
> last query are each in their own cell, copy / pasting them into the
> spreadsheet will result in three rows and four columns (Server, Pct_Used1,
> Pct_Used2, Pct_Used3).
>
> I suspect LISTAGG isn't what I'm looking for, but I'm stumped as to what
> the solution is.  Well, LISTAGG *could* work, if I could specify a tab
> character vs. ', ' as the second argument.  Not sure what EBCDIC value
> would translate as a tab character when pasting to Excel.
>
> Select * from qtemp/WRKASP ;   (Results below, dashes are nulls)
> Seq   Server            ASP#   Dev_Name      Pct_Used
> 1       ABCAA1              1      -                          56.550
> 2       TAA01B              1      -                           24.670
> 2       TAA01B          144     TAA01IASP          80.210
> 3       PAB03B              1      -                           23.040
> 3       PAB03B            33     BONCAM            71.440
> 3       PAB03B            34     IVRYASP                          -
> 3       PAB03B          200     CAMOPS             83.440
>
> select Server,
> ListAgg( Pct_Used, ', ' ) within group (order by ASP_NUMBER) as
> All_Pct_Used
>  from qtemp/WRKASP
> Group by Seq, Server;   (Results below)
> Server              All_Pct_Used
> ABCAA1           56.550
> TAA01B            24.670, 80.210
> PAB03B            23.040, 71.440, 83.440
>
>
> *** CONFIDENTIALITY NOTICE: The information contained in this
> communication may be confidential, and is intended only for the use of the
> recipients named above. If the reader of this message is not the intended
> recipient, you are hereby notified that any dissemination, distribution, or
> copying of this communication, or any of its contents, is strictly
> prohibited. If you have received this communication in error, please return
> it to the sender immediately and delete the original message and any copy
> of it from your computer system. If you have any questions concerning this
> message, please contact the sender. ***
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.