Wide code doesn't look good

Question

Post reply

Wide code doesn't look good

Thom A

SSC Guru

Points: 99130

April 12, 2019 at 7:00 pm

#3167008

The title says it all, but I pasted some SQL, which is pretty wide, and it looks really bad on the forums:

USE Sandbox;
GO

CREATE TABLE TEMP(TID varchar(100),
                  FName varchar(100),
                  LName varchar(100),
                  LDate date);
INSERT INTO TEMP VALUES ('878A','user1','user2','20190101');
INSERT INTO TEMP VALUES ('878A','user1','user2','20190301');
INSERT INTO TEMP VALUES ('878A','user1','user3','20190401');

GO
--SELECT *
--FROM TEMP;

GO
DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT V.COLUMNS,' + NCHAR(13) + NCHAR(10) +
           STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
                         N'       MAX(CASE T.LDATE WHEN ' + QUOTENAME(CONVERT(varchar(8),T.LDATE,112),'''') + N' THEN CASE V.COLUMNS ' +
                         STUFF((SELECT NCHAR(13) + NCHAR(10) +
                                       N'                                                            WHEN N' + QUOTENAME(C.COLUMN_NAME,'''') + N' THEN CONVERT(varchar(100),' + QUOTENAME(C.COLUMN_NAME) + N')'
                                FROM INFORMATION_SCHEMA.COLUMNS C
                                WHERE TABLE_NAME = N'TEMP'
                                  AND COLUMN_NAME != N'LDATE'
                                ORDER BY ORDINAL_POSITION
                                FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,62,N'') + NCHAR(13) + NCHAR(10) +
                         N'                                             END' + NCHAR(13) + NCHAR(10) +
                         N'           END) AS ' + QUOTENAME(CONVERT(varchar(10),LDATE,103))
                  FROM TEMP T
                  GROUP BY LDATE
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + NCHAR(13) + NCHAR(10) +
           N'FROM TEMP T' + NCHAR(13) + NCHAR(10) +
           N'     CROSS APPLY (VALUES' + STUFF((SELECT N',(N' + QUOTENAME(COLUMN_NAME,'''') + N')'
                                                FROM INFORMATION_SCHEMA.COLUMNS
                                                WHERE TABLE_NAME = N'TEMP'
                                                  AND COLUMN_NAME != N'LDATE'
                                                ORDER BY ORDINAL_POSITION ASC
                                                FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') + N') V(COLUMNS)' + NCHAR(13) + NCHAR(10) +
           N'GROUP BY V.COLUMNS;';

PRINT @SQL; --Your debugging best friend
EXEC sp_executesql @SQL;

GO
DROP TABLE TEMP;

Conversely, in Azure Data Studio, it looks fine to read (attached image). ASD version

Could the code windows have a horizontal scroll bar, if the area the posts use isn't going to get larger? Otherwise complex code like this really is an unreadable mess. It would be good if that is applied to the articles as well, if they "suffer" from the same problem.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 1

Added this to the bug for styling. I'm torn on this, as sometimes really long code starts to break down in styling. If we can get a side scrolling section, that might be best.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Agreed on the horizontal scrollbar.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions