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?

/*
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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.