SQL Server : get size of longest value in column and include column name in that total

Question

I have the following query that returns the number of the longest word it finds within that column:

SELECT 
    MAX(LEN(id)) AS id, 
    MAX(LEN(linkToTbl2)) AS linkToTbl2, 
    MAX(LEN(description)) AS description, 
    MAX(LEN(number)) AS number, 
    MAX(LEN(line)) AS line, 
    MAX(LEN(network)) AS network, 
    MAX(LEN(type)) AS type, 
    MAX(LEN(IPhase)) AS IPhase, 
    MAX(LEN(environment)) AS environment, 
    MAX(LEN(sType)) AS sType, 
    MAX(LEN(bDescription)) AS bDescription
FROM 
    bLine

However, if said column is smaller than the heading for that column then it doesn't take that into account when calculating the largest value.

Example (what I am looking to do):

|id | linkToTbl2 | description             |
+---+------------+-------------------------+
|14 |hi          |This is just a demo.     |
|16 |baa         |Another description here.|

Which would look like this in an example query:

|id |linkToTbl2 |description |
+---+-----------+------------+
|2  |10         |25          |

Now this is what it currently looks like in my SSRS report:

|id |lin|description              |
|   |kTo|                         |
|   |tbl|                         |
|   |2  |                         |
|---|---|-------------------------|
|14 |hi |This is just a demo.     |
|16 |baa|Another description here.|

and this would look like this in the query:

|id |linkToTbl2 |description |
|---|-----------|------------|
|2  |3          |25          |

Notice how the linkToTbl2 field is compressed since the longest value in that column is 3 (baa). linkToTbl2 would be 10 (linkToTbl2) so it should be 10 and not 3.

How can I add the columns name into the query to count that as well?

Can't you do this at the application level? It seems simpler there. — Gordon Linoff, Feb 02 '18 at 18:29
@GordonLinoff it would be easier to do that but in my current case, it would be best to do it via the query. — StealthRT, Feb 02 '18 at 18:30

Serkan Arslan · Accepted Answer · 2018-02-02T19:06:58.530

3

You can use UNPIVOT and PIVOT

DECLARE @MyTable TABLE (id INT,  linkToTbl2 VARCHAR(100),  description  VARCHAR(100))
INSERT INTO @MyTable VALUES
(14,'hi','This is just a demo.'),
(16,'baa','Another description here.')


SELECT * FROM 
    ( SELECT 
        Col,  
        MAX(CASE WHEN LEN(Val) > LEN(Col) THEN LEN(Val) ELSE LEN(Col) END) LEN_OF_COL  
      FROM 
        ( SELECT 
                CONVERT(VARCHAR(MAX),[Id]) [Id], 
                CONVERT(VARCHAR(MAX),[linkToTbl2]) [linkToTbl2], 
                CONVERT(VARCHAR(MAX),[description]) [description] 
            FROM @MyTable ) SRC
        UNPIVOT (Val FOR Col IN( [Id], [linkToTbl2], [description] ) ) UNPVT
    GROUP BY Col ) T 
PIVOT( MAX(LEN_OF_COL) FOR Col IN ( [Id], [linkToTbl2], [description] ) ) PVT

Result:

Id          linkToTbl2  description
----------- ----------- -----------
2           10          25

edited Feb 02 '18 at 19:06

answered Feb 02 '18 at 18:49

Serkan Arslan

13,158
4
29
44

I'm getting this error: **The type of column "linkToTbl2" conflicts with the type of other columns specified in the UNPIVOT list.** – StealthRT Feb 02 '18 at 19:04
All columns should be the same type. I made an update – Serkan Arslan Feb 02 '18 at 19:08
Ah that did it. Thanks for the help, Serkan! – StealthRT Feb 02 '18 at 19:10
I wondering why you need this and original query doesnt work? – Juan Carlos Oropeza Feb 02 '18 at 19:11
@JuanCarlosOropeza it does work but does not take into account the columns name in the count. – StealthRT Feb 02 '18 at 19:15

Gordon Linoff · Answer 2 · 2018-02-03T03:18:22.647

0

If your columns are all strings, you could use the rather brute force:

select . . .
from ((select id, linkToTbl2, . . . from bLine) union all
      (select 'id', 'linkToTbl2', . . .)
     ) b;

Whatever approach you take in SQL will require listing all the column names. I think this is better done in the application layer.

edited Feb 03 '18 at 03:18

answered Feb 02 '18 at 18:31

Gordon Linoff

1,242,037
58
646
786

I get **All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.** – StealthRT Feb 02 '18 at 18:41
@StealthRT . . . Then list the columns explicitly. – Gordon Linoff Feb 03 '18 at 03:18

SQL Server : get size of longest value in column and include column name in that total

2 Answers2