How to CONCATENATE distinct string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to CONCATENATE distinct string

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-26 : 15:55:26

Hello I have a query to CONCATENATE the column:

select coalesce(cast(IDName as varchar)+ ',', '')from
from the student

I have the results:
IDName
11111,
11111,
11111,
22222,
22222,
22222

However, I only want distinct IDName such as 11111 and 22222

select coalesce(cast(distinct IDName as varchar)+ ',', '')from
from the student

I got syntax error. How to I achieve this?

Your help is always appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-26 : 16:05:23

[code]-- setup
DECLARE @t TABLE(IDName VARCHAR(10))
INSERT @t SELECT
11111 UNION ALL SELECT
11111 UNION ALL SELECT
11111 UNION ALL SELECT
22222 UNION ALL SELECT
22222 UNION ALL SELECT
22222
-- end setup

-- query
SELECT STUFF((SELECT DISTINCT ','+idname
FROM @t FOR XML PATH('')),1,1,'')[/code]Just change the reference to @t to match your table name.

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-26 : 16:32:52

Thank you very much for the quick response. I have date condition.

SELECT STUFF((SELECT DISTINCT ','+idname
FROM @t FOR XML PATH('')),1,1,'')
where date >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

It gave me the error: Msg 207, Level 16, State 1, Line 3
Invalid column name 'date'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'date'. but my @t did has column date

Where I missed?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-26 : 16:56:05

SELECT STUFF((
SELECT DISTINCT ','+idname
FROM @t
WHERE DATE >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND DATE < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
FOR XML PATH(''))
,1,1,'')

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-06-26 : 17:21:07

Thank you very much! You are super!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources