Assigning DISTINCT value to a variable in SQL

Question

I have a dataset with one of the columns as Transaction_Date in which date varies from the year 2005 to 2018.

I need to assign a distinct year to a variable, later on, I will be using the same variable in SQL Pivot.

-- variable declaration

DECLARE @PCOL VARCHAR(20);

-- assigning values to the variable

SELECT @PCOL += (QUOTENAME (X.TD) + ',')
FROM 
(
    SELECT DISTINCT YEAR(TRANSACTION_DATE) AS TD 
    FROM TRANSACTION_INFO
) AS  X;

-- Check the result

PRINT @PCOL

It is not resulting me the output as expected. Please suggest.

So what *is* the result you are getting and what is wrong with it? We can't run your SQL and you haven't explained what's wrong or what you want. — Thom A, Oct 30 '18 at 13:44
It's returning NULL I bet since your didn't set it to an empty string. But doing this still wouldn't give you ALL years... you only have varchar(20) for one. You are going to get truncated. Anywho, Yogesh answer is what you want for string concat. — S3S, Oct 30 '18 at 13:46
Possible duplicate of [How to make a query with group\_concat in sql server](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — S3S, Oct 30 '18 at 13:48
I suspect you're right @scsimon. `NULL + {expression} = NULL` — Thom A, Oct 30 '18 at 13:48

Yogesh Sharma · Accepted Answer · 2018-10-30T13:52:57.170

2

You need FOR XML PATH() clause :

SELECT @PCOL = STUFF( (SELECT DISTINCT ', '+ QUOTENAME(CAST(YEAR(TRANSACTION_DATE) AS VARCHAR(255)))
                       FROM TRANSACTION_INFO
                       FOR XML PATH('')
                      ), 1, 1, ''
                    )

edited Oct 30 '18 at 13:52

answered Oct 30 '18 at 13:46

Yogesh Sharma

49,870
5
26
52

1

@JohnCappelletti. . . Yes if that variable would be used in `Dynamic` SQL, then it is required. – Yogesh Sharma Oct 30 '18 at 13:54

score 2 · Answer 2 · answered Oct 30 '18 at 13:52

2

You're getting NULL because you didn't pre-set your variable to an empty string before you used the += operator.

Since NULL + 'some value' = NULL, your variable never gets changed from NULL to something else.

answered Oct 30 '18 at 13:52

Tab Alleman

31,483
7
36
52

score 1 · Answer 3 · answered Oct 30 '18 at 14:03

It looks like this is what you're attempting to do...

IF OBJECT_ID('tempdb..#TRANSACTION_INFO', 'U') IS NOT NULL 
BEGIN DROP TABLE #TRANSACTION_INFO; END;

CREATE TABLE #TRANSACTION_INFO (
    TRANSACTION_DATE DATE
    );
INSERT #TRANSACTION_INFO (TRANSACTION_DATE) VALUES
    ('20130101'),('20130101'),('20140101'),
    ('20140102'),('20150102'),('20150102'),
    ('20160103'),('20160103'),('20170104'),
    ('20170104'),('20180105'),('20180105');

--================================================

DECLARE @POL VARCHAR(200) = '';

SELECT 
    @POL = CONCAT(@POL, ',', x.TD)
FROM (
    SELECT DISTINCT 
        TD = YEAR(ti.TRANSACTION_DATE)
    FROM
        #TRANSACTION_INFO ti
    ) x;

SET @POL = STUFF(@POL, 1, 1, '');

PRINT(@POL);

Result:

2013,2014,2015,2016,2017,2018

Assigning DISTINCT value to a variable in SQL

3 Answers3