Dynamic SQL not Capturing all Columns

Question

I am trying to capture a set of columns, but the set can change depending on the data in question (it changes every month). Because of this, I am working on a way to capture the distinct values in the Service column, and put the column names separated by commas into a variable to call later. Here is the code (parsed down to only what is important).

DECLARE @cols AS NVARCHAR(MAX);

SELECT @cols = ISNULL(@cols + ',','') + QUOTENAME([Service])
FROM(
    SELECT DISTINCT Billing_Table.[Service]
    FROM Billing_Table
    ) AS Services;

The issue is that when I run

SELECT DISTINCT Billing_Table.[Service]
FROM Billing_Table

by itself, I get all of the distinct services listed (which is good, it's what I am expecting). But the variable @cols does not contain all of the same columns, which is not good.

Any reason why this is? What can I modify to fix this?

Answer 1

The reason your code did not work is because you are using the variable @cols recursively. It will not work like that with SQL. (To the best of my knowledge, recursion in T-SQL must be done through CTE).

Try this

    SELECT @cols = STUFF((
        SELECT DISTINCT CONCAT(',',[Service]) FROM Billing_table
        FOR XML PATH(''),type).value('.','NVARCHAR(MAX)'),1,1,'')

Test:

DECLARE @tmp TABLE (
    ID INT
);

DECLARE @cols NVARCHAR(MAX);

INSERT INTO @tmp (ID)
VALUES
(1), (2), (3);

SELECT @cols = STUFF((
    SELECT DISTINCT CONCAT(',',ID) FROM @tmp
    FOR XML PATH(''),type).value('.','NVARCHAR(MAX)'),1,1,'')

SELECT @cols;