Merge the rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Merge the rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-08 : 08:18:48

krishnan writes "Hai,
i have a query to select a field.when i selecting,for example there are two rows retrieved.whats my need is after select i will joined the two rows in coding to make it single.Is it possible in query what i doing in programm.

pls help me"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-08 : 08:39:50

Give an example of the "2 row" resultset and the desired "merged" resultset.

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-08 : 10:08:53

Yes as long as the rows have a relationship...you'd just join them... and there's some criteria that sets each row apart...

SELECT *
FROM myTable a
INNER JOIN myTable b
ON a.[id] = b.[id]
WHERE a.col1 = 'apple'
AND b.col1 = 'banana'

OK, can I open my eyes now?

Hope that bullet didn't hit anyone....

Brett

8-)

SELECT POST=NewId()

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-08 : 10:12:53

What the man is asking for is something like this :

SomeResult
----------
ILONA
ODONATA

and he wants it like :

SomeResult
----------
ILONA, ODONATA etc etc

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-08 : 10:50:59

Using COALESCE to Build Comma-Delimited String

Jay White
{0}

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-09 : 03:02:53

Hi Krishnan,
U can try the following query, but there is a limitation on the size of varchar. So this would work if the resultant data length is between 0 and 8000 .

Note: Replace @table with the actual Table name and columns respectively.

Declare @concatstring varchar(8000)
Declare @table table (EmployeeID int, EmployeeName varchar(50), StatusDate datetime)
Insert into @table select 1,'Joe','05/02/2003'
Insert into @table select 1,'Joe','04/01/2002'
Insert into @table select 1,'Joe','07/25/2001'
Insert into @table select 2,'Jim','05/20/2003'
Insert into @table select 2,'Jim ','06/20/2002'
Insert into @table select 2,'Joe','04/01/2002'
set @concatstring=''
select @concatstring=@concatstring+ EmployeeName + ','
from @table

select
case when charindex(',',@concatstring) > 0 then substring(@concatstring,1,len(@concatstring) -1) else @concatstring end

V.Ganesh
NetAssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources