Group fields in select - 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 2005 Forums

Transact-SQL (2005)

Group fields in select

Author

Topic

hztm2
Starting Member

16 Posts

Posted - 2010-06-21 : 10:39:44

Hi, I have comments in a table and I want to try and group these. Eg

Job Comment
1 Comment1
2 Comment2
1 Comment3

I want to query this so that I can combine the notes for Job 1 - so I get each note separated by a space or vbcrlf - so I get back something like

Job
1 Comment1
Comment3
2 Comment2

Thank you very much indeed for any assistance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-21 : 11:59:35

Try this:

DECLARE @Sample TABLE (JOB INT, COMMENT VARCHAR(500))

INSERT @Sample
SELECT 1, 'Comment1' UNION ALL
SELECT 2, 'Comment2' UNION ALL
SELECT 1, 'Comment3'

-- Show the expected output
SELECT DISTINCT s1.JOB,
STUFF((SELECT DISTINCT TOP 100 PERCENT + CHAR(10) + s2.COMMENT FROM @Sample AS s2 WHERE s2.JOB = s1.JOB ORDER BY + CHAR(10) + s2.COMMENT FOR XML PATH('')), 1, 1, '') AS COMMENTS
FROM @Sample AS s1
ORDER BY s1.JOB

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

daryljmorris
Starting Member

3 Posts

Posted - 2010-06-21 : 13:41:32

Why would you just not user a nested cursor???

Daryl J Morris
MCP
MCTS: SQL 2005

Kristen
Test

22859 Posts

Posted - 2010-06-21 : 14:49:27

"Why would you just not user a nested cursor???"

Cursors are very slow - would be a reason.

daryljmorris
Starting Member

3 Posts

Posted - 2010-06-21 : 14:51:04

use emisii
set nocount on
Go

declare @job int,
@comment char(20)

Declare job_cu Cursor For
Select Distinct job
From job_comment
Order By job

Open job_cu

Fetch Next From job_cu Into @job

While @@FETCH_STATUS = 0
Begin
Print '***************'
Print @job
Print '_______________'

Declare comment_cu Cursor For
Select Distinct comment
From job_comment
Where job = @job
Order By comment

Open comment_cu

Fetch Next From comment_cu Into @comment

While @@FETCH_STATUS = 0
Begin

Print @comment

Fetch Next From comment_cu Into @comment
End

Close comment_cu
Deallocate comment_cu

Fetch Next From job_cu Into @job

End

Close job_cu
Deallocate job_cu

Get the Results of

***************
1
_______________
comment 1
comment 2
***************
2
_______________
comment 1
comment 2

Daryl J Morris
MCP
MCTS: SQL 2005

daryljmorris
Starting Member

3 Posts

Posted - 2010-06-21 : 14:54:23

Try this:

DECLARE @Sample TABLE (JOB INT, COMMENT VARCHAR(500))

INSERT @Sample
SELECT 1, 'Comment1' UNION ALL
SELECT 2, 'Comment2' UNION ALL
SELECT 1, 'Comment3'

-- Show the expected output
SELECT DISTINCT s1.JOB,
STUFF((SELECT DISTINCT TOP 100 PERCENT + CHAR(10) + s2.COMMENT FROM @Sample AS s2 WHERE s2.JOB = s1.JOB ORDER BY + CHAR(10) + s2.COMMENT FOR XML PATH('')), 1, 1, '') AS COMMENTS
FROM @Sample AS s1
ORDER BY s1.JOB

I have never used that though and it works pretty good. This is why I signed up on here lol.

Daryl J Morris
MCP
MCTS: SQL 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-22 : 04:51:02

quote:
Originally posted by hztm2

Hi, I have comments in a table and I want to try and group these. Eg

Job Comment
1 Comment1
2 Comment2
1 Comment3

I want to query this so that I can combine the notes for Job 1 - so I get each note separated by a space or vbcrlf - so I get back something like

Job
1 Comment1
Comment3
2 Comment2

Thank you very much indeed for any assistance.

This is known as Suppress if duplicated feature
Do this in your reporting tool

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-22 : 04:56:23

quote:
Originally posted by daryljmorris

Why would you just not user a nested cursor???

Daryl J Morris
MCP
MCTS: SQL 2005

Why would you just not use a reporting tool???

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources