MS SQL

How to combine multiple rows's column into one column

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2007

Posts: 26

Reputation: nokomoli is an unknown quantity at this point

Solved Threads: 0

nokomoli

Offline

Light Poster

How to combine multiple rows's column into one column

Jul 2nd, 2009

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?

•

Join Date: Feb 2009

Posts: 3,134

Reputation: sknake has much to be proud of

Solved Threads: 551

sknake

Offline

.NET Enthusiast

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

Jul 2nd, 2009

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
/*
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
/*
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