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?
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