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

我有以下查询返回它在该列中找到的最长单词的数量:

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

但是，如果所述列的 比该列的标题小 则在计算最大值时不会考虑到这一点。

示例(我想要做什么):

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

在示例查询中看起来像这样:

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

现在这就是我的 SSRS 报告中目前的样子:

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

这在查询中看起来像这样:

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

请注意 linkToTbl2 字段是如何压缩的，因为该列中的最长值是 3 (baa)。 linkToTbl2 将是 10 (linkToTbl2) 所以它应该是 10 而不是 3。

如何将列名添加到查询中来计算它？

最佳答案

您可以使用 UNPIVOT 和 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

结果:

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

关于SQL Server : get size of longest value in column and include column name in that total，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48588787/