Concatenating Row Values

Hi,
I am trying to return some data from one table. I would like to see every thing grouped by Customer Number, on one row,so even though a customer may have mulitiple products, these will appear beside each other in different columns rather then in different rows.

I have been trying to concat the data so that i can show it row by row but without sucess of getting the sturcture i need.

I would like to see the date like this;

Cus No, Prod 1, Price1, New Produc, New Price1. Prodcut2, Price2, New Product 2,New Price2, Address....

A customer could have one or 20 products on their account.

So i really want to see what the existing prod a customer has against the new one i have against them , and the same with the price.

Can any one advise me please.

This is what i have tried so far, but it gives me all my products in one field  seperated by commas, and when i try to get the value for the price, its just Summing rather then giving me individual prices.

Please let me know if you need anymore information.

Thanks,
putoch

CREATE TABLE [dbo].[temp_post](
	[ServiceId] [float] NULL,
	[CustomerNumber] [float] NULL,
	[Noofoccurances] [float] NULL,
	[ExistingProduct] [nvarchar](255) NULL,
	[ExistingMonthlyFee] [money] NULL,
	[NewProduct] [nvarchar](255) NULL,
	[NewMonthlyFee] [money] NULL,
	[ServiceAddress1] [nvarchar](255) NULL
	) ON [PRIMARY]
 
 
insert into temp_post values (22182,100015,4,'Breeze 4MB Lite 24:1',70,'Breeze 6Mb 24:1'80,SomeAddress)
insert into temp_post values (22184,100015,4,'Breeze 4MB 12:1',135,'Breeze 6Mb Plus 12:1',150,SomeAddress)
insert into temp_post values (48265,100015,4,'Breeze 4MB 12:1',135,'Breeze 6Mb Plus 12:1',150,SomeAddress)
insert into temp_post values (88935,100015,4,'Breeze 4MB 12:1',135,'Breeze 6Mb Plus 12:1',150,SomeAddress)
 
 
--I've tried using this code but its not giving me what i really want
 
WITH Ranked ( CustomerNumber2, rnk, ExistingProduct  ,NewProduct , ExistingMonthlyFee )  
 
             AS ( SELECT CustomerNumber2,
 
                         ROW_NUMBER() OVER( PARTITION BY CustomerNumber2 ORDER BY CustomerNumber2 ),
 
                         CAST( ExistingProduct AS VARCHAR(8000) ),
						ExistingMonthlyFee,
						 CAST ( NewProduct AS VARCHAR (8000))
 
                    FROM temp_post),
 
   AnchorRanked ( CustomerNumber2, rnk, ExistingProduct,ExistingMonthlyFee,NewProduct )  
 
             AS ( SELECT CustomerNumber2, rnk, ExistingProduct, 
						ExistingMonthlyFee,NewProduct 
 
                    FROM Ranked
 
                   WHERE rnk = 1 ),
 
RecurRanked ( CustomerNumber2, rnk, ExistingProduct,
						ExistingMonthlyFee, NewProduct ) 
 
             AS ( SELECT CustomerNumber2, rnk, ExistingProduct,
						ExistingMonthlyFee,NewProduct 
 
                    FROM AnchorRanked
 
                   UNION ALL 
 
                  SELECT Ranked.CustomerNumber2, Ranked.rnk,
 
                         RecurRanked.ExistingProduct + ', ' + Ranked.ExistingProduct,
						RecurRanked.ExistingMonthlyFee	+',  '+ Ranked.ExistingMonthlyFee,
                         RecurRanked.NewProduct + ', '+ Ranked.NewProduct  
 
 
                    FROM Ranked
 
                   INNER JOIN RecurRanked 
 
                      ON Ranked.CustomerNumber2 = RecurRanked.CustomerNumber2 
 
                     AND Ranked.rnk = RecurRanked.rnk + 1 )
 
SELECT CustomerNumber2, MAX( ExistingProduct ),MAX(ExistingMonthlyFee)
 
      FROM RecurRanked 
 
  GROUP BY CustomerNumber2;

Select allOpen in new window