How to remove empty columns from query output

Transact-SQL
1

Can someone help modify the query below to remove empty columns
For example, image you can column 'Last year end' is empty of values.
image

This table was produced with the following code.

SELECT
  DatesTestv3.as_of_date
 ,CASE
    WHEN DatesTestv3.as_of_date = EOMONTH(DATEADD(MONTH, -1, SYSDATETIME())) THEN 'LM'
  END AS 'Last Month End'
 ,CASE
    WHEN DatesTestv3.as_of_date = EOMONTH(DATEADD(QUARTER, -1, DATEFROMPARTS(YEAR(SYSDATETIME()), (MONTH(SYSDATETIME()) / 4 + 1) * 3, 1))) THEN 'LQ'
  END AS [Last quarter end]
 ,CASE
    WHEN DatesTestv3.as_of_date = DATEFROMPARTS(YEAR(SYSDATETIME()) - 1, 12, 31) THEN 'LY'
  END AS [Last year end]
FROM dbo.DatesTestv3

I would like the query modified so that it removes empty columns such that the output would look like the following:
image

I have included the sample table

CREATE TABLE #tmpTable (
    as_of_date date,
    ColA varchar(50),
    ColB varchar(50))

INSERT #tmpTable VALUES
(CONVERT(DATETIME, '2023-05-31', 120),'LM','A'),
(CONVERT(DATETIME, '2023-03-31', 120),'LQ','B'),
(CONVERT(DATETIME, '2021-12-31', 120),'LY','C'),
(CONVERT(DATETIME, '2023-04-30', 120),'LM2','D'),
(CONVERT(DATETIME, '2022-09-30', 120),'LQ2','E'),
(CONVERT(DATETIME, '2021-12-31', 120),'LY2','F')

SELECT * FROM #tmpTable

Thanks

2

Not possible - and why would you want to do that anyways?

3

That's a rather extreme blanket statement to make.

I added an empty column to your sample table, otherwise how would we know if the code successfully removed a column(s)? Uncomment ColD to test removing multiple columns at once.


DROP TABLE IF EXISTS #tmpTable;
CREATE TABLE #tmpTable (
    as_of_date date NOT NULL,
    ColA varchar(50) NULL,
    ColB varchar(50) NULL,
    ColC varchar(10) NULL /*, ColD int NULL */ ) --<<--
INSERT #tmpTable ( as_of_date, ColA, ColB ) VALUES
    (CONVERT(DATETIME, '2023-05-31', 120),'LM','A'),
    (CONVERT(DATETIME, '2023-03-31', 120),'LQ','B'),
    (CONVERT(DATETIME, '2021-12-31', 120),'LY','C'),
    (CONVERT(DATETIME, '2023-04-30', 120),'LM2','D'),
    (CONVERT(DATETIME, '2022-09-30', 120),'LQ2','E'),
    (CONVERT(DATETIME, '2021-12-31', 120),'LY2','F')

SELECT 'before DROP COLUMN(s)', * FROM #tmpTable;

--******************************************************************************

DECLARE @column_list nvarchar(max)
DECLARE @sql nvarchar(max)

SELECT @sql = (SELECT 
    REPLACE('+CASE WHEN COUNT(*) = SUM(CASE WHEN [$column$] IS NULL ' +
        'THEN 1 ELSE 0 END) THEN '',[$column$]'' ELSE '''' END', 
        '$column$', c.name)
    FROM tempdb.sys.columns c
    WHERE c.is_nullable = 1 AND c.is_computed = 0 AND c.object_id = OBJECT_ID('tempdb.dbo.#tmpTable')
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')

SELECT @sql = 'SELECT @column_list = ' + STUFF(@sql, 1, 1, '') + ' FROM #tmpTable'

EXEC sys.sp_executesql @sql, N'@column_list nvarchar(max) OUTPUT', @column_list OUTPUT

IF LEN(@column_list) > 1
BEGIN
    SET @sql = 'ALTER TABLE #tmpTable DROP COLUMN ' + STUFF(@column_list, 1, 1, '')
    SELECT @sql
    EXEC(@sql)
    SELECT 'after DROP COLUMN(s)', * FROM #tmpTable
END /*IF*/

--******************************************************************************
4

Such a useful information