Grouping Results of a Query small problem

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

SQL Server Development (2000)

Grouping Results of a Query small problem

Author

Topic

jdaly
Starting Member

1 Post

Posted - 2006-07-11 : 11:03:00

I created a report based off of two views
so that I could pull data regarding the leads and the notes regarding those leads, below is the SQL statement I use to do this

SELECT
dbo.FilteredLead.companyname, dbo.FilteredLead.new_productname,
dbo.FilteredLead.new_clientbusinesstypename, dbo.FilteredLead.subject,
dbo.FilteredAnnotation.notetext, dbo.FilteredLead.fullname,
dbo.FilteredLead.description, dbo.FilteredLead.leadsourcecodename,
dbo.FilteredLead.owneridname

FROM
dbo.FilteredAnnotation INNER JOIN
dbo.FilteredLead ON dbo.FilteredAnnotation.objectid = dbo.FilteredLead.leadid

WHERE
(dbo.FilteredLead.leadsourcecodename = 'Quote & Bound Report')

ORDER BY dbo.FilteredLead.companyname

Now this statement works out pretty well and returns the data that I want
with one small problem. If a lead has more than one note associated with it the results will have the company listed twice. Looking something like below

Companyname1 Note A
Companyname2 Note A
Companyname2 Note B
Companyname2 Note C
Companyname3 Note A
Companyname3 Note B

This isnt too huge of a deal but I would liek to have it so each company only has one heading with all the notes listed under it, like.

Companyname1 Note A
Companyname2 Note A
Note B
Note C
Companyname3 Note A
Note B

Any ideas or help is appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-11 : 11:11:08

Can you handle this in the front end application ? SQL Server is not meant to perform this type of data formatting.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-11 : 11:11:57

Can you not do this in whatever you are using for the report?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-11 : 12:34:05

+1 for doing this in the front-end.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-11 : 13:24:24

Oh well

set nocount on
SELECT
dbo.FilteredLead.companyname, dbo.FilteredLead.new_productname,
dbo.FilteredLead.new_clientbusinesstypename, dbo.FilteredLead.subject,
dbo.FilteredAnnotation.notetext, dbo.FilteredLead.fullname,
dbo.FilteredLead.description, dbo.FilteredLead.leadsourcecodename,
dbo.FilteredLead.owneridname ,
identity(int,1,1) as id
into #a
FROM
dbo.FilteredAnnotation INNER JOIN
dbo.FilteredLead ON dbo.FilteredAnnotation.objectid = dbo.FilteredLead.leadid
WHERE
(dbo.FilteredLead.leadsourcecodename = 'Quote & Bound Report')

select case when id = (select min(id) from #a a2 where a2.companyname = a.companyname) then companyname else '' end,
, new_productname, new_clientbusinesstypename, ......
from #a a
ORDER BY dbo.FilteredLead.companyname, id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources