How to combine multiple rows's column into one column
 

Hello, i have face problem when i need to retrieve data from database (SQL 2000). Below my table :

Column A | Column B
1 a
1 b
2 c
2 d

i need the result is :

Column A | Column B
1 a b
2 c d

Can anyone tell me how to write the sql command?

Re: How to combine multiple rows's column into one column
 

/*

Column A | Column B

1 a 

1 b

2 c

2 d

*/



IF OBJECT_ID('Table1', 'U') IS NOT NULL DROP TABLE Table1

Create Table Table1

(

  ColumnA int,

  ColumnB varchar(1)

)



GO



Insert Into Table1 (ColumnA, ColumnB) Values (1, 'a')

Insert Into Table1 (ColumnA, ColumnB) Values (1, 'b')

Insert Into Table1 (ColumnA, ColumnB) Values (2, 'c')

Insert Into Table1 (ColumnA, ColumnB) Values (2, 'd')



GO



GO



IF OBJECT_ID('dbo.JoinString', 'FN') IS NOT NULL DROP FUNCTION dbo.JoinString



GO



CREATE FUNCTION dbo.JoinString (@ColumnA int)

RETURNS varchar(100)

AS

BEGIN



  Declare @result varchar(100)

  Set @result = ''

  Select @result = @result + ' ' + IsNull(ColumnB, '')

  From Table1

  Where ColumnA = @ColumnA

  Order By ColumnB



  return LTRIM(@result)

END



GO



Declare @Tab Table

(

  ColumnA int,

  ColumnB varchar(100)

)



Insert Into @Tab (ColumnA) Select Distinct(ColumnA) From Table1 (NOLOCK)

Update @Tab Set ColumnB = dbo.JoinString(ColumnA)





Select *

From @Tab