Click here to Skip to main content
15,560,600 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

The below sql statement print user id, article id and notes. Now one user can have notes in different articles. I want to list all the notes under same articles together instead of repeating it.

For instance,

uid aid title notes
1 2 article1 test
1 2 article1 testing
1 3 article2 something else

So I want to display notes with article title -

Title
notes

i.e.
article 1
test
testing

article 2
something else

SQL
SELECT uid, aid, note
FROM notes_manager
WHERE uid = '123456'
ORDER BY dtime DESC


There must be an easy solution to this, just not striking me at the moment.
Thanks
Posted
Updated 11-Mar-13 19:41pm
v4
Comments
Manfred Rudolf Bihy 11-Mar-13 9:04am    
Not clear what you mean by not "repeating it". We have no idea what your UI looks like or what it is meant to look like. You'll have to explain a bit more what you are expecting.
Make clear from your SQL example what the expected output is supposed to be.
AndyInUK 11-Mar-13 11:23am    
I have updated the question. Hope it makes sense now.

Hi,

Check the sample script...

SQL
-- User Define Function
CREATE FUNCTION [dbo].[fnSplitString](
    @Input VARCHAR(8000)					-- List of delimited items
  , @Delimiter VARCHAR(8000) = ','				-- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@Delimiter,@Input,0) <> 0
BEGIN
	SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@Input,1,CHARINDEX(@Delimiter,@Input,0)-1))),
	@Input=RTRIM(LTRIM(SUBSTRING(@Input,CHARINDEX(@Delimiter,@Input,0)+LEN(@Delimiter),LEN(@Input))))

	IF LEN(@sItem) > 0
		INSERT INTO @List SELECT @sItem
	END

	IF LEN(@Input) > 0
		INSERT INTO @List SELECT @Input -- Put the last item in
	RETURN
END


SQL
-- Sample Query
DECLARE @articleDtls TABLE(uid INT, aid INT, title VARCHAR(30), notes VARCHAR(100))
DECLARE @strArticle VARCHAR(MAX)

INSERT INTO @articleDtls (uid, aid, title, notes)
VALUES(1, 2, 'article1', 'test'),
(1, 2, 'article1', 'testing'),
(1, 3, 'article2', 'something')
SELECT uid, aid, title, notes FROM @articleDtls 


SELECT @strArticle=STUFF((SELECT  ','+A.title AS [text()]
FROM(SELECT TOP 100 PERCENT T.aid, T.title+(SELECT ','+ notes AS [text()] FROM @articleDtls WHERE aid=T.aid FOR XML PATH('')) 'title' 
	 FROM (SELECT DISTINCT aid, title FROM @articleDtls) T
	 ORDER BY T.aid) A
FOR XML PATH('')),1,1,'') 

SELECT Item FROM dbo.fnSplitString(@strArticle,',')


Regards,
GVPrabu
 
Share this answer
 
Hello,

Does below SQL helps
SQL
SELECT uid, aid, notes FROM notes_manager ORDER BY uid, aid, dtime DESC

It will basically select article wise notes with latest note appearing first for all users. If you want to do this for a particular user then add WHERE clause.

Regards,
 
Share this answer
 
Comments
AndyInUK 11-Mar-13 11:22am    
I think I didn't explained the question well - I want to display result like -

article 1
test
testing

article 2
something else

With the above query, I end up repeating title for each notes under same article
Prasad Khandekar 11-Mar-13 22:52pm    
Hello,

There are two things. One in selecting data in proper order & second is to display that in a particular way. The suggested query will help you retrieve the data in desired order. For display purpose you can use any hierarchical datagrid control. Have a look at custom controls from infragistics.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900