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

5

hi

hope this helps

Another Way of doing this

declare @countA int 
declare @strA varchar(20) = 'ColA'
select @countA = sum(case when ColA is null then 0 else 1 end) from tmpTable


declare @countB int 
declare @strB varchar(20) = 'ColB'
select @countB = sum(case when ColB is null then 0 else 1 end) from tmpTable

declare @countC int 
declare @strC varchar(20) = 'ColC'
select @countC = sum(case when ColC is null then 0 else 1 end) from tmpTable

declare @DySQL nvarchar(max) = ''

set @DySQL = 'SELECT '

if @countA > 0  
set @DySQL = @DySQL + @StrA +' '+','

if @countB > 0  
set @DySQL = @DySQL + @StrB+ ' '+','

if @countC > 0  
set @DySQL = @DySQL + @StrC+ ' '+','

set @DySQL = @DySQL + ' FROM tmpTable '

set @DySQL =  replace(@DySQL,', FROM','FROM')

EXEC sys.sp_executesql @DySQL

image

6

SQL is set up to scale vertically rather than horizontally so to achieve this, we need to create a dynamic query to change the number of columns rather than the number of rows returned. There are multiple ways of doing this but here is my take on it.

First I figure out what columns are not entirely NULL. This is achieved by summing up the total number of rows that are not null and then setting a flag to 1 for a non 0 count and 0 for columns that have no values.

Next, the STRING_AGG function is used to dynamically build the SQL query. Then it is executed with the EXECUTE (@Querry)

--------------------------------------------------
-- Get a list of columns that are not entirely null

-- Drop temp table if already exists
DROP TABLE IF EXISTS #NonNullColumns 

-- Get a list of columns that are not entirely null and insert them into a temp table
SELECT columnName, hasValues
INTO #NonNullColumns
FROM
(
	SELECT	CASE WHEN SUM(CASE WHEN  as_of_date IS NULL THEN 0 ELSE 1 END) > 0 THEN 1 ELSE 0 END AS as_of_date
			,CASE WHEN SUM(CASE WHEN ColA IS NULL THEN 0 ELSE 1 END) > 0 THEN 1 ELSE 0 END AS ColA
			,CASE WHEN SUM(CASE WHEN ColB IS NULL THEN 0 ELSE 1 END) > 0 THEN 1 ELSE 0 END AS ColB
			,CASE WHEN SUM(CASE WHEN NullTest IS NULL THEN 0 ELSE 1 END) > 0 THEN 1 ELSE 0 END AS NullTest
	FROM	#tmpTable
) p
UNPIVOT
(
	hasValues FOR columnName IN (as_of_date, ColA, ColB, NullTest)
) AS UNP

--------------------------------------------------
-- Dynamically build a select query

DECLARE @Query AS VARCHAR(MAX)

SELECT	@Query = 'SELECT ' + STRING_AGG(columnName, ',') + ' FROM #tmpTable '
FROM	#NonNullColumns
WHERE   hasValues = 1

EXECUTE (@Query)