0

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?

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by sknake
0
/*
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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.