Hi All,

I have an issue. I hope you people can help me in fixing this issue.

I have a table with the name GuestDetails with the Columns BookingID, GuestName, DOB

I shoule be able to combine GuestName,DOB, with respect to column and row. Can any one help me with this regard.

I mean to say, if the data is displayed in this format in the table

BookingId   GuestName   DOB

1                  Arun             14/05

1                 Sandhya         12/06

2                   Akhil             18/09

3                    Sirisha           15/85

3                  Sandeep           14/85

I should get the data like this.

Arun,14/05|Sandhya,12/06|

Akhil,18/09|

Sandeep,14/85|Sirisha 15/85|

Cany any one let me know "How to achieve this", I will be very greatful to you, if you people can help me with this regard.

Thanks and Regards,

G.V.N.Sandeep

Please check suggestions from this thread http://tek-tips.com/viewthread.cfm?qid=1554659

Try this:

Declare @a table (BookingId int, GuestName Varchar(20), dob varchar(10))

Insert Into @a
Select 1, 'Arun',    '14/05' UNION ALL
Select 1, 'Sandhya', '12/06' UNION ALL
Select 2, 'Akhil',   '18/09' UNION ALL
Select 3, 'Sirisha', '15/85' UNION ALL
Select 3, 'Sandeep', '14/85' ;
   
With tmp as 
(Select BookingId,
        Row_Number() Over (Partition by BookingId Order By GuestName) as rn,
        GuestName + ',' + DOB  as string
   From @a
)
Select a.string, b.string
  From (Select * from tmp Where rn=1) a  
  Left Join (Select * from tmp Where rn=2) b 
         on a.BookingId=b.BookingId

Or for only one string per row:

With tmp as 
(Select BookingId,
        Row_Number() Over (Partition by BookingId Order By GuestName) as rn,
        GuestName + ',' + DOB  as string
   From @a
)
Select a.string +'|' + isNull(b.string+'|', '') as final
  From (Select * from tmp Where rn=1) a  
  Left Join (Select * from tmp Where rn=2) b 
         on a.BookingId=b.BookingId;

result is:

Arun,14/05|Sandhya,12/06|
Akhil,18/09|
Sandeep,14/85|Sirisha,15/85|

I think the idea was to concatenate all of them together and also without limitation of 2 rows maximum per group.

This sample demonstrates the limitation of the above approach

   Declare @a table (BookingId int, GuestName Varchar(20), dob varchar(10))  
      
   Insert Into @a  
   Select 1, 'Arun',    '14/05' UNION ALL  
   Select 1, 'Sandhya', '12/06' UNION ALL  
   Select 1, 'Third person', '12/06' UNION ALL  
   Select 2, 'Akhil',   '18/09' UNION ALL  
   Select 3, 'Sirisha', '15/85' UNION ALL  
   Select 3, 'Sandeep', '14/85' ;  
         
  With tmp as   
  (Select BookingId,  
           Row_Number() Over (Partition by BookingId Order By GuestName) as rn,  
           GuestName + ',' + DOB  as string  
     From @a  
  )  
  Select a.string + coalesce('|' + b.string,'') as Info  
  From (Select * from tmp Where rn=1) a    
  Left Join (Select * from tmp Where rn=2) b   
  on a.BookingId=b.BookingId  

With SQL Server 2000...

declare @guesttable table(bookingid int, guestname varchar(20), dob varchar(5))

insert into @guesttable
select  1, 'Arun', '14/05'
union all select 1, 'Sandhya', '12/06'
union all select 2, 'Akhil', '18/09'
union all select 3, 'Sirisha', '15/85'
union all select 3, 'Sandeep', '14/85'

declare @count_table table (counter int identity(1,1), bookingid int)
declare @finaltable table (names varchar(200))
declare @finalvalue varchar(4000), @seperator char, @maxcount int, @counter int

set @seperator = '|'
set @counter = 1



insert into @count_table (bookingid)
select bookingid from @guesttable group by bookingid

select @maxcount = count(*) from @count_table

while @counter <= @maxcount
begin

	select @finalvalue = coalesce(@finalvalue, '') + guestname + ',' + dob + @seperator
		from  @guesttable gt
			inner join @count_table ct on gt.bookingid = ct.bookingid and ct.counter = @counter

	insert into @finaltable select @finalvalue
	
	set @counter = @counter + 1

	set @finalvalue = null

end

select * from @finaltable

I do not have crystal bowl

Create table z9test (BookingId int, GuestName Varchar(20), dob varchar(10))
GO

Insert Into z9test
Select 1, 'Arun',    '14/05' UNION ALL
Select 1, 'Sandhya', '12/06' UNION ALL
Select 1, 'Sandhya1', '12/06' UNION ALL
Select 2, 'Akhil',   '18/09' UNION ALL
Select 3, 'Sirisha', '15/85' UNION ALL
Select 3, 'Sandeep', '14/85' ;

GO

Create Function z9Concatenate(@num int)
Returns varchar(500) as
Begin
  Declare @str varchar(500)
  Set @str=''
  Select @str=@str+GuestName+','+DoB+'|'
    From z9test
   Where BookingId = @num
   Order By GuestName
  Return (@str)
End
GO

Select dbo.z9Concatenate(BookingId)
  From (Select Distinct BookingId From z9test) a

RESULT:

Arun,14/05|Sandhya,12/06|Sandhya1,12/06|
Akhil,18/09|
Sandeep,14/85|Sirisha,15/85|

SQL 2005 and up solution based on the http://knowledgebaseworld.blogspot.com/2008/12/string-concatenation-in-sql.html

  SELECT
        B.BookingId,
        (SELECT  GuestName+','+ DoB + '|'  FROM z9test WHERE BookingID=[B].BookingId FOR XML PATH('')) AS GuestInfo
        FROM
        (
            SELECT DISTINCT BookingID FROM z9test
        )B

Can you please test execution plans difference on big sets?

Thanks a lot in advance.

Niikola, did you ever had a chance to test what performs better? I just had a need to implement this myself, I chose the XML solution.

I did some testing and I couldn't see difference in execution. I should try to do test against really big table and a big number of rows to concatenate, butI do not expect spectacular results.

I like XML solution, but you should document it in the code because it is not obvious what you will get as result (for others who will read your code)

I'm not sure I fully understand it myself :) But I gave a link in my code to this discussion.

BTW, I just sent you an e-mail with the actual code.