SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

future_is_me
Starting Member

USA
14 Posts

Posted - 11/26/2012 :  11:47:57  Show Profile  Reply with Quote
I have a table with structure as`

CREATE TABLE [dbo].[2012_RtHs]( [Date] [datetime] NOT NULL, [RtHs] [int] NOT NULL ) ON [PRIMARY]

GO

The sample output is listed below:
Date RtHs
2012-11-23 28
2012-11-22 4
2012-11-22 28
2012-11-18 15
2012-11-18 35
2012-11-17 36
2012-11-17 26

I need the query that returns the out in the below format:

Date RtHs
2012-11-23 28
2012-11-22 4,28
2012-11-18 15,35
2012-11-17 36,26

Is this possible?

Thanks

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/26/2012 :  12:16:04  Show Profile  Reply with Quote
Can use XML PATH as shown below:
SELECT
	a.Date,
	STUFF(b.Rs,1,1,'') AS RtHS
FROM
	(SELECT DISTINCT Date FROM [dbo].[2012_RtHs]) a
	CROSS APPLY
	(
		SELECT
			',' AS [text()],
			b.[RtHs] AS [text()]
		FROM
			[dbo].[2012_RtHs] b
		WHERE
			b.Date = a.Date
		FOR XML PATH('')
	) b(Rs);
Go to Top of Page

future_is_me
Starting Member

USA
14 Posts

Posted - 11/26/2012 :  14:54:35  Show Profile  Reply with Quote
Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000