Need help with a query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Need help with a query

Author

Topic

future_is_me
Starting Member

14 Posts

Posted - 2012-11-26 : 11:47:57

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 12:16:04

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);

future_is_me
Starting Member

14 Posts

Posted - 2012-11-26 : 14:54:35

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources