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?
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.