How to get results of all dates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to get results of all dates?

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-12-29 : 01:55:06

Dear Experts,

I have a table named ExamDates
It has 2 columns Module, DateOfExam

e.g.
Module, DateOfExam
Fashion Design , 2 Feb 2009
Fashion Design, 30 Nov 2008
Materials, 4 Dec 2008
Materials, 6 Jan 2009
Materials, 10 Feb 2009

What i want is to concentrate the dates to return only 1 row for each module for mutiple dates separated by comma,

e.g.
'Fashion Design', '2 Feb 2009, 30 Nov 2008'
'Materials' , '4 Dec 2009, 6 Jan 2009, 10 Feb 2009'

How do I do this?

This information is for me to preview and ensure that all dates are correct only - and it is not connected to any code at the moment. I am hoping to get the data above and export for excel for the user for further processing.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-29 : 02:08:40

select module,stuff((select ',' + convert(varchar(32),DateOfExam) from examdates where module = t.module for xml path('')),1,1,'') from examdates t

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-12-29 : 02:56:30

Hi bklr,

this is not working. When I try this, it returns.
Fashion Design, '2 Feb 2009, 30 Nov 2008, 4 Dec 2009, 6 Jan 2009, 10 Feb 2009'
Materials, '2 Feb 2009, 30 Nov 2008, 4 Dec 2009, 6 Jan 2009, 10 Feb 2009'

i.e. It does not group by the Module.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-29 : 03:25:56

it is working check it once
DECLARE @tbl TABLE ( Module VARCHAR(32), DateOfExam VARCHAR(32) )
INSERT INTO @tbl
SELECT 'Fashion Design', '2 Feb 2009' UNION ALL
SELECT 'Fashion Design', '30 Nov 2008' UNION ALL
SELECT 'Materials', '4 Dec 2008' UNION ALL
SELECT 'Materials', '6 Jan 2009' UNION ALL
SELECT 'Materials', '10 Feb 2009'
select DISTINCT module,stuff((select ',' + DateOfExam from @tbl where module = t.module for xml path('')),1,1,'') from @tbl t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 04:28:54

quote:
Originally posted by EugeneLim11

Hi bklr,

this is not working. When I try this, it returns.
Fashion Design, '2 Feb 2009, 30 Nov 2008, 4 Dec 2009, 6 Jan 2009, 10 Feb 2009'
Materials, '2 Feb 2009, 30 Nov 2008, 4 Dec 2009, 6 Jan 2009, 10 Feb 2009'

i.e. It does not group by the Module.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

i think posted query should work. if its not working,post the query you used.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-29 : 04:54:12

hi,eugene
i think u have forgot to keep where condition in the query so that u got that o\p
check once

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-12-29 : 20:43:19

Hi bklr,

I found the problem. It is my fault and your code is working.

Once I selected the distinct one, all duplicates are removed. For some reason it show duplicates in my database, perhaps because I got too many records.

Regards

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources