grouping and concatenating - 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

Transact-SQL (2000)

grouping and concatenating

Author

Topic

hybridGik
Starting Member

26 Posts

Posted - 2006-06-29 : 20:31:17

hi again.

i have an existing table with an ID and Description

ID Desc
-----------
A BC
A DE
A FG
B BC
B DE
B FG

what i need is to select the records from the table and have an output exactly to:

ID Desc
----------
A BCDEFG
B BCDEFG

how will i do it?

pls post a query.

thanks.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-29 : 20:57:40

Assuming that there will be variable number of rows with the one ID, you'll need to do this in a loop.
Probably easier to do it in the front-end if possible. It's certainly possible to do it in T-SQL but you'll need to use a WHILE loop to do it.

HTH,
Tim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 21:33:24

if you want to do this in SQL then see here http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

hybridGik
Starting Member

26 Posts

Posted - 2006-06-30 : 00:09:35

Yes it could be done with a loop.
I post this type of problem here because there are really hardcore sql programmers out there that could do that without using a loop or temp table. Maybe they will use Having clause or whatsoever.

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-30 : 03:01:25

Read the link content. Function is used and called for each row

Madhivanan

Failing to plan is Planning to fail

hybridGik
Starting Member

26 Posts

Posted - 2006-06-30 : 16:45:13

problem solved. with this.

DECLARE @MaxSEQNumber int
DECLARE @Counter int
DECLARE @strSQL varchar(8000)
DECLARE @strSQL1 varchar(8000)

SELECT @strSQL = ''
SELECT @strSQL1 = ''
SELECT @MaxSEQNumber = Max(SEQNumber) From IM_03ItemEndedDescription
SET @Counter = 1
WHILE @Counter <= @MaxSEQNumber
BEGIN
IF @strSQL <> ''
SET @strSQL = @strSQL + ','
SET @strSQL = @strSQL + ' MIN( CASE WHEN
SEQNumber = ' + CAST(@counter as varchar(5)) + '
THEN ISNULL(CommentText,'''') END ) AS X' + CAST(@counter as varchar(5))

SET @Counter = @Counter +1
END

SET @strSQL = 'SELECT ItemNumber, ' + @strSQL + ' FROM IM_03ItemEndedDescription GROUP BY ItemNumber'
SET @Counter = 1
--PRINT @strSQL
--PRINT @Counter
--PRINT @MaxRank
WHILE @Counter <= @MaxSEQNumber
BEGIN
IF @strSQL1 <> ''
SET @strSQL1 = @strSQL1 + ' + '' '' + '
SET @strSQL1 = @strSQL1 + ' ISNULL(A.X' + CAST(@counter as varchar(5)) + ','''')'
SET @Counter = @Counter +1
END

--PRINT @strSQL1

SET @strSQL1 = 'SELECT A.ItemNumber, ' + @strSQL1 + ' as CommentText FROM ( ' + @strSQL + ') A order by ItemNumber'
--////////////////////////////////////////

EXEC (@strSQL1)
--PRINT @strSQL1

thanks.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 05:37:11

hybridGik - Given the original problem description, that method looks quite complicated. This might be simpler (and is one of the methods described in the link)...

--data
create table MyTable (ID varchar(10), [Desc] varchar(10))
insert MyTable
          select 'A', 'BC'
union all select 'A', 'DE'
union all select 'A', 'FG'
union all select 'B', 'BC'
union all select 'B', 'DE'
union all select 'B', 'FG'

--function
go
create function dbo.fn_MyTableIdDescriptions (@ID varchar(10)) returns varchar(8000) as
begin
    declare @Descs varchar(8000)
    select @Descs = isnull(@Descs, '') + [Desc] from MyTable where ID = @ID
    return @Descs
end
go

--calculation
select ID, dbo.fn_MyTableIdDescriptions(ID) as [Desc] from MyTable group by ID

--tidy
drop table MyTable
drop function dbo.fn_MyTableIdDescriptions

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources