Get the maximum text width of a column inside SELECT statement

Question

I'm convinced someone else must have had this same issue before but I just can't find anything.

Given a table of data:

DECLARE @Table TABLE
(
    [COL_NAME] nvarchar(30) NOT NULL,
    [COL_AGE] int NOT NULL
);

INSERT INTO @Table
SELECT N'Column 1', 4 UNION ALL
SELECT N'Col2', 2 UNION ALL
SELECT N'Col 3', 56 UNION ALL
SELECT N'Column Four', 8 UNION ALL
SELECT N'Column Number 5', 12 UNION ALL
SELECT N'Column Number Six', 9;

If I use SSMS and set my output to text, running this script:

SELECT [COL_AGE], [COL_NAME] AS [MyCol] FROM @Table

Produces this:

COL_AGE     MyCol
----------- -----------------
4           Column 1
2           Col2
56          Col 3
8           Column Four
12          Column Number 5
9           Column Number Six

Note that the data is neatly formatted and spaced.

I want to display the contents like SQL does when you post your results to text:

'Column 1         '
'Col2             '
'Col 3            '
'Column Four      '
'Column Number 5  '
'Column Number Six'

The following is just to describe what I want, I understand it's obviously a horrible piece of script and should never make its way into production:

SELECT 
    N'''' + LEFT( 
        [COL_NAME] + SPACE( ( SELECT MAX(LEN([COL_NAME])) FROM @Table ) )
        , ( SELECT MAX(LEN([COL_NAME])) FROM @Table ) 
    ) + N'''' 
FROM @Table

Originally, I tried this script, which is what I'm trying to get right:

SELECT 
    N'''' + LEFT( 
        [COL_NAME] + SPACE(MAX(LEN([COL_NAME])))
        , MAX(LEN([COL_NAME])) 
    ) + N''''
FROM @Table

But it returns the following error:

Msg 8120, Level 16, State 1, Line 28 Column '@Table.COL_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The script is part of a much bigger script and it all has to happen within the SELECT statement, I can't use external variables to first look up the MAX(LEN()) because the bigger script iterates through other tables.

Any help would be appreciated.

Answer 1

I don't really get, what you are trying to achieve, but I think, this might be what you need:

DECLARE @Table TABLE
(
    [COL_NAME] nvarchar(30) NOT NULL
);


INSERT INTO @Table
SELECT N'Column 1' UNION ALL
SELECT N'Col2' UNION ALL
SELECT N'Col 3' UNION ALL
SELECT N'Column Four' UNION ALL
SELECT N'Column Number 5' UNION ALL
SELECT N'Column Number Six';

--The query

WITH ComputeMaxLength(ml) AS(SELECT MAX(LEN(t.COL_NAME)) FROM @Table t)
SELECT LEFT(CONCAT(tbl.COL_NAME,REPLICATE('.',ml)),ml)
FROM ComputeMaxLength
CROSS JOIN @Table tbl;

The result

Column 1.........
Col2.............
Col 3............
Column Four......
Column Number 5..
Column Number Six

The idea in short:

We use a CTE to compute the needed value.
Then we CROSS JOIN this with the source table. As the CTE will return just one row in any case, this will not add rows to our result. But it will add a column to our result, which we can use to compute a padding...

Hint: I used a dot instead of a blank to make the effect visible

UPDATE Cannot use a WITH / CTE

You can shift the computation into an APPLY like here

SELECT CONCAT('''',LEFT(CONCAT([COL_NAME],SPACE(ml)),ml),'''')
FROM @Table
CROSS APPLY(SELECT MAX(LEN([COL_NAME])) FROM @Table) A(ml);

The engine should be smart enough to execute this just once...

UPDATE 2: If you cannot use an APPLY either

Your own code, which is obviously a horrible piece of script and should never make its way into production, is not bad (just ugly :-) )

I'd put it like this

SELECT CONCAT(''''
             ,LEFT(CONCAT([COL_NAME]
                         ,SPACE((SELECT MAX(LEN([COL_NAME])) FROM @Table)))
                  ,(SELECT MAX(LEN([COL_NAME])) FROM @Table))
             ,'''')
FROM @Table;

The engine should be smart enough to reuse the result in this case too (and avoid multiple executions of the SELECT MAX()......

Answer 2

I just used a quick CROSS APPLY to get the length of the buffer you want to use:

select
    N'''' + LEFT( 
        [COL_NAME] + SPACE( t2.MLEN )
        , t2.MLEN 
    ) + N''''
from @Table
CROSS APPLY ( SELECT MAX(LEN([COL_NAME])) MLEN FROM @Table )  t2