[b]Getting results from multiple rows to be displayed on same line[/b]

[Nassy]

Hi

I have a table [Property Details] which shows property details and the details of the vendors selling each property
eg

Prop ID Address Vendor Name Property Status
------- ------- ----------- ---------------
1 3 Popp close Mrs Taylor Under Offer
1 3 Popp close Mr Taylor Under Offer
1 3 Popp close Mr Daimlar Under Offer
2 51 Bramble way Mr Dott Exchanged
2 51 Bramble way Mrs Langley Exchanged
3 2 Turnips rd Ms Louis Marketing

I am trying to create a report for mail merge purposes that has all the details of each property on one line with all the vendor names alongside it eg

Prop ID Address Vendor1 Vendor2 Vendor3
------- ------- ------- ------- -------
1 3 Popp close Mrs Taylor Mr Taylor Mr Daimlar

How do I achieve this? It wouldn't be so bad if I could be sure that not more that 2 vendors could be assigned to a property, but I definitely can't assume this.

Would correlated subqueries help my cause?

Any help on this would be truly appreciated as I am totally stumped.



Nassy

 

[john76]

I'm not sure of the coding off the top of my head but a recursive select is generally the way to pivot row data into column data when the number of records is unknown.

I'll see if I can find some example code.

John

 

[Katy44]

I have wondered about this before, and haven't been able to find anything on it. I was looking for an aggregate string function, e.g.:

select PropID, CONCATENATE(VendorName)
From Property Details
Group by PropID

but haven't found anything like it.
Does anyone know of anything like this?

 

[ttba]

Here's an article on dynamic pivot tables that may be of help:

http://www.sqlteam.com/item.asp?ItemID=2955

 

[Nassy]

Thanks for all the replies. So incredibly quick - I love this forum!

I'll have a look at some of the suggestions and let you know what I come up with.

Nassy

 

[sunila7]

I am not sure this will help.. but this will basically concatenate all the data for vendor name and display on one line.
This will not give the names of vendor as separate fields but a comma separated list of vendor names.

/*SELECT Stmnt*/
select PropID,Mzx(Address) Address,dbo.Concatenate(PropId) ConcatFLD from TBLName
Group By PropID

/*Concatenate Function*/
CREATE FUNCTION dbo.Concatenate (@PropID int)  
RETURNS varchar(8000) AS  
BEGIN 

Declare @ConVendorName varchar(8000)
set @ConVendorName = ''
Select @ConVendorName = @ConVendorName+ VendorName + ',' From TBLName Where PropID = @PropID
return Left(@ConVendorName,len(@ConVendorName)-1)
END

Sunil

 

[john76]

OK, I got some code from page 384 of "Microsoft SQL Server 2000 Bible" by Paul Nielsen and did some testing:

[green]-- Note:  This can be cut and pasted into Query Analyzer without modification and run as is.[/green]
Use tempdb
Go

Set NoCount On

[green]-- Set up the testing table[/green]
If Exists( Select  1
             From  sysobjects
             Where Name = 'RecursiveSelectTest'
               And Type = 'U'
) Drop Table RecursiveSelectTest

Create Table RecursiveSelectTest
(
 RowID  int,
 Field1 varchar(10)
)

[green]-- Insert some test values[/green]
Insert Into RecursiveSelectTest
  Values (1, 'Record1')
Insert Into RecursiveSelectTest
  Values (2, 'Record2')
Insert Into	RecursiveSelectTest
  Values (3, 'Record3')
Insert Into	RecursiveSelectTest
  Values (4, 'Record4')
Insert Into	RecursiveSelectTest
  Values (5, 'Record5')

[green]-- Check the table data[/green]
Select  *
  From  RecursiveSelectTest

[green]-- Initialize the variable to denormalize the list into[/green]
Declare @RecursiveSelect varchar(1000)
Set @RecursiveSelect = ''

[green]-- Denormalize the list[/green]
[green]-- Note that this is where the "action" is happening out of all of this code[/green]
Select  @RecursiveSelect = @RecursiveSelect + derived.Field1 + ', '
  From ( Select  Field1
           From  RecursiveSelectTest
       ) As derived

[green]-- Print out the denormalized variable[/green]
[green]-- Note that I'm using the Left function to cut out the trailing ","[/green]
Print	Left(@RecursiveSelect, Len(@RecursiveSelect)-1)
Go

And it worked beautifully.

Adapting this to your particular problem will take a little more work, but I think it can be done.

I'll keep at it.

John

 

[Nassy]

Thank you so much John - I'll play around with this code and see how I get on - I'll let you know if I have any problems.

Thanks again

Nassy

 

[john76]

Glad I could help, try it out and see if you can adapt it to your situation, I'd try and finish but another problem just got dumped on me ( this one from my boss, I guess it takes priority ;-) ).

Gotta go for now.

John