How to remove empty columns / fields from output

Question

Hello Community,

I have received great help from community members here to get to the result I've reached at the moment.

I would like help modifying my query such that it removes empty columns.

The image shows the field 'Last year end' does not return any values after executing 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 

Can someone help modify the code such that empty columns don not appear in the output?

Therefore the output would look like the following after modification of the query

A sample of the table is below

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

Any help much appreciated.

Answer 1

Hi,

Please check if this solve your need

DECLARE @SQL NVARCHAR(MAX)
;With MyCTE AS(
	-- This is your query inside the CTE
	SELECT   as_of_date  ,CASE     WHEN as_of_date = EOMONTH(DATEADD(MONTH, -1, SYSDATETIME())) THEN 'LM'   END AS 'Last Month End'  ,CASE     WHEN 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 as_of_date = DATEFROMPARTS(YEAR(SYSDATETIME()) - 1, 12, 31) THEN 'LY'   END AS [Last year end] FROM #tmpTable
)
SELECT @SQL = 'SELECT ' + CONCAT_WS(',',
	'[as_of_date]',
	IIF(Max([Last quarter end]) IS NOT NULL,'[Last quarter end]',NULL),
	IIF(Max([Last year end]) IS NOT NULL,'[Last year end]',NULL),
	IIF(Max([Last Month End]) IS NOT NULL,'[Last Month End]',NULL)
	) + ' FROM #Tbl'
FROM #Tbl
EXECUTE sp_executesql @SQL

Answer 2

Hi @Carlton Patterson

Can someone help modify the code such that empty columns don not appear in the output?

In a SELECT query, you could use condition filters only for the rows, not for the columns.

You might perform dynamically create statements to achieve this, but this is not a recommended thing from a SQL performance perspective.

If you use SSRS, then you could go to the column, right click and select 'Column Visibility'.

Select show or hide based on expression and give the expression as:

=IIF(Max(Field, Dataset)= "",TRUE,FALSE)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I would like help modifying my query such that it removes empty columns.

Then remove the column from your SELECT statement; what's the problem to do so?