One row for each user per Author

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

One row for each user per Author

Author

Topic

mihirvb84
Starting Member

11 Posts

Posted - 2012-03-15 : 11:16:40

Hi Team,

I have combined below data of Reader number, Book and sections of that book, date of reading in TempRP table (as shown in the table below). Now in this table there are some duplicate entries as well. Now I want to get one row per reader for each author with combining all sections read into one cell (as shown in the table below) and duplicates should be omitted. I tried several options but didn't work as expected. Please share if have any idea.

TempRP table data:
Reading Date Reader# Author Section Read
3/15/2012 1 A Section 1
3/15/2012 1 A Section 2, Section 3
3/15/2012 1 A Section 1
3/15/2012 1 A Section 2, Section 3
3/15/2012 1 B Section 1
3/15/2012 1 B Section 1
3/15/2012 1 C Section 1

I want result like below.
Reading Date Reader# Author Section Read
3/15/2012 1 A Section 1,Section 2,Section 3
3/15/2012 1 B Section 1
3/15/2012 1 C Section 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-15 : 11:43:47

If you are on SQL 2005 or higher, you can use the XML PATH approach like this:

SELECT DISTINCT
	ReadingDate,
	[Reader#],
	Author,
	STUFF(sections,1,1,'') AS Sections
FROM
	TempRP a
	CROSS APPLY
	(
		SELECT ','+[Section Read] AS [text()]
		FROM TempRP b
		WHERE a.ReadingDate = b.ReadingDate
		AND a.[Reader#] = b.[Reader#]
		AND a.Author = b.Author
		FOR XML PATH('')
	) b(sections);

mihirvb84
Starting Member

11 Posts

Posted - 2012-03-15 : 13:33:20

Thanks for looking into this, Sunita. I am using SQL higher than 2005 but Unfortunately this is not working. I am getting all rows in the result. Additionally when using the query below, in the section column it is not combining sections like "section 1,2,3" but giving the word section only.

quote:

Originally posted by sunitabeck

If you are on SQL 2005 or higher, you can use the XML PATH approach like this:

SELECT DISTINCT
	ReadingDate,
	[Reader#],
	Author,
	STUFF(sections,1,1,'') AS Sections
FROM
	TempRP a
	CROSS APPLY
	(
		SELECT ','+[Section Read] AS [text()]
		FROM TempRP b
		WHERE a.ReadingDate = b.ReadingDate
		AND a.[Reader#] = b.[Reader#]
		AND a.Author = b.Author
		FOR XML PATH('')
	) b(sections);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:18:06

[code]
SELECT [Reading Date], [Reader#], [Author],
STUFF((SELECT DISTINCT ',' + [Section Read] FROM table WHERE [Reading Date]=t.[Reading Date]
AND [Reader#]= t.[Reader#]
AND [Author]=t.[Author] ORDER BY ','+ [Section Read] FOR XML PATH('')),1,1,'') AS [Section Read]
FROM (SELECT DISTINCT [Reading Date], [Reader#], [Author] FROM table)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mihirvb84
Starting Member

11 Posts

Posted - 2012-03-16 : 09:35:26

Thanks for looking into this, Visakh. But the query below is not combining "Section Read". It gives all rows and a separate row for each section read. The query result also displays the duplicate rows. Here we need just one row for each Reader per contributors and with all sections read together in the same row.

quote:
Originally posted by visakh16

SELECT [Reading Date], [Reader#], [Author],
STUFF((SELECT DISTINCT ',' + [Section Read] FROM table WHERE [Reading Date]=t.[Reading Date]
AND [Reader#]= t.[Reader#]
AND [Author]=t.[Author] ORDER BY ','+ [Section Read] FOR XML PATH('')),1,1,'') AS [Section Read]
FROM (SELECT DISTINCT [Reading Date], [Reader#], [Author] FROM table)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:00:46

i think it should work unless you've some other columns also involved which you've not shown us so far

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources