Aligning data in output

[morganjk]

I recently created a select statement that will give me info similar to a "q session". I figured out how to convert the data_sent and data_received columns. What I can't find is how to right align these columns

Here's a sample of the current output:

SESSION # STATE DATA SENT DATA RECVD SESS TYPE
----------- --------- ----------- ----------- ----------
759632 Run 207.4 MB 52.5 KB Admin
761840 Run 15.4 MB 217 B Admin
776005 Run 103.5 KB 346.2 GB Node
786930 IdleW 109.3 KB 8.6 GB Node
795628 IdleW 376.0 MB 24.4 KB Node
797109 Run 2.5 MB 205 B Admin
798535 IdleW 4.6 GB 186.3 KB Node
823243 Run 143 B 231 B Admin


What I would like to see

SESSION # STATE DATA SENT DATA RECVD SESS TYPE
----------- --------- ----------- ----------- ----------
759632 Run 207.4 MB 52.5 KB Admin
761840 Run 15.4 MB 217 B Admin
776005 Run 103.5 KB 346.2 GB Node
786930 IdleW 109.3 KB 8.6 GB Node
795628 IdleW 376.0 MB 24.4 KB Node
797109 Run 2.5 MB 205 B Admin
798535 IdleW 4.6 GB 186.3 KB Node
823243 Run 143 B 231 B Admin

Select statement used.
select cast((SESSION_ID) as char(10)) as "SESSION #", -
cast((STATE) as char(8)) as "STATE", -
CASE -
WHEN BYTES_SENT>1099511627776 THEN CAST(DEC(BYTES_SENT)/1024/1024/1024/1024 AS DEC(5,1))||' TB' -
WHEN BYTES_SENT>1073741824 THEN CAST(DEC(BYTES_SENT)/1024/1024/1024 AS DEC(5,1))||' GB' -
WHEN BYTES_SENT>1048576 THEN CAST(DEC(BYTES_SENT)/1024/1024 AS DEC(5,1))||' MB' -
WHEN BYTES_SENT>1024 THEN CAST(DEC(BYTES_SENT)/1024 AS DEC(5,1))||' KB' -
ELSE -
CAST(BYTES_SENT AS DEC(5,0))||' B' -
END AS "DATA SENT", -
CASE -
WHEN BYTES_RECEIVED>1099511627776 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024/1024/1024 AS DEC(5,1))||' TB' -
WHEN BYTES_RECEIVED>1073741824 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024/1024 AS DEC(5,1))||' GB' -
WHEN BYTES_RECEIVED>1048576 THEN CAST(DEC(BYTES_RECEIVED)/1024/1024 AS DEC(5,1))||' MB' -
WHEN BYTES_RECEIVED>1024 THEN CAST(DEC(BYTES_RECEIVED)/1024 AS DEC(5,1))||' KB' -
ELSE -
CAST(BYTES_RECEIVED AS DEC(5,0))||' B' -
END AS "DATA RECVD", -
cast((SESSION_TYPE) as char(7)) as "SESS TYPE" -
from SESSIONS order by SESSION_ID

 

[moon-buddy]

I don't think there is a "right-align" option for SQL-type select commands unless you get this formatted after.

 

[marclant]

Text is normally left aligned and numbers right aligned. Other than that, I don't think there's more control than that with SQL. When you append the unit type (KB, MB, etc.) with the number, that becomes text.

I personally prefer to use the same unit/scale for the entire column and just put the unit type in the column heading. Because this way, it's easy by looking at the length of the number which one is larger. With your method, which does have some advantages too, forces the reader to also check the unit when comparing two rows. It's also easier to sort if you import the output in Excel.

The advantage of your method is that large and small numbers are always represented small with the unit. If you go with GB or TB, small numbers always appear as 0. If you go with KB or MB, then large numbers always have several digits.