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

Transact-SQL (2000)

Combine Rows

Author

Topic

cblatchford
Starting Member

40 Posts

Posted - 2006-06-12 : 11:57:55

Hi Chaps,

I have the following query

select (feeEarners.pplchristianname) + ' ' +(feeEarners.pplsurname) as [Fee Earner],
(secretaries.pplchristianname) + ' ' +(secretaries.pplsurname) as [Secretary]
from secretarylink
inner join people as FeeEarners
on slnfeeearnerid = FeeEarners.pplid
inner join people as Secretaries
on slnsecretaryid = secretaries.pplid
order by [Fee Earner]

This query gives me a list of fee earners (solicitors!) and their associated secretaries. Now obviously some fee earners have more than one secretary, as below:

Column1 Column2
Fee Earner 1 Secretary 1
Fee Earner 1 Secretary 2

Is there anyway I can combine the rows/fields thus:

Column1 Column2
Fee Earner 1 Secretary 1, Secretary 2

Cheers

Blatcho

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-12 : 16:52:00

Yes, search the forum for crosstab or pivoting techniques.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-13 : 01:48:59

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

cblatchford
Starting Member

40 Posts

Posted - 2006-06-13 : 03:51:48

Thanks Madhivanan and Peso, good karma to you both!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-13 : 04:01:37

quote:
Originally posted by cblatchford

Thanks Madhivanan and Peso, good karma to you both!

What does karma mean?

Madhivanan

Failing to plan is Planning to fail

cblatchford
Starting Member

40 Posts

Posted - 2006-06-13 : 04:13:09

From Wikipedia:

"Karma literally means action. We first take an intent and follow it up with action in the form of thought, speaking to others, planning and execution. According to the Theory of Karma - every action taken to achieve a result or profit (fruit of action) leaves a trace or a karmic residue behind it. It is this residue that keeps accumulating and takes away ones well being and freedom. Only a person who acts to produce results without worrying about profit (Nishkama Karma or Niskarma) does not accumulate such a Karmic residue. Such action normally comes out of love or pure passion."

Seeing as you helped me without worrying about profit, you have good karma!

cblatchford
Starting Member

40 Posts

Posted - 2006-06-13 : 04:42:54

Hi again..

I'm still having a problem understanding the crosstab technique, could someone help out? Take the data below..

Bryony Cove Sidonia Nash
Bryony Cove Keren Harrison

How can I combine the rows so they look like

Bryony Cove Sidonia Nash, Keren Harrison

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-13 : 05:23:38

Read the link I suggested. Cross tab is not suitable for this problem

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources