How can I obtain all rows from this query

Question

How do I return the values i want?

I know the distinct value is messing up result but dont know how to fix

Declare @ColumnNames NVARCHAR(MAX) = '';
Declare @today as date; Set @today = getdate();

Select distinct @ColumnNames +=  QuoteName(cast(date as varchar(100))) + ','
From Dates
where date >= @today

Print @ColumnNames

Results are

[2017-10-17],

but should be

[2017-10-11],[2017-10-12],[2017-10-13],[2017-10-14],[2017-10-15],[2017-10-16],[2017-10-17],

Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — RoMEoMusTDiE, Oct 12 '17 at 02:14
Possible duplicate of [Concatenate many rows into a single text string?](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Valli, Oct 12 '17 at 02:42

nobody · Answer 1 · 2017-10-12T03:14:26.373

0

Remove the '+',use coalesce and use distinct before using coalesce.Note in my case the where clause is where date > 2017-10-04

select distinct convert(varchar(10),date,23) as new_date
into #temp1
from dates

declare @columnnames nvarchar(max)
select  @columnnames =  coalesce(@columnnames+ ', ', '') + new_date 
from #temp1  

drop table #temp1
print @columnnames

Output

edited Oct 12 '17 at 03:14

answered Oct 12 '17 at 03:02

nobody

10,892
8
45
63

This method may fail. Try adding `distinct` in `select` it will fail sometimes. Always use for xml path('') to concatenate rows to csv – Pரதீப் Oct 12 '17 at 04:10

How can I obtain all rows from this query

1 Answers1