March 4, 2003 at 4:01 pm
How can I consolidate the many records in SALEINFO column that belongs to one ID record.
I have a result set that looks like this:
Column1:
ID
10069905,7/31/2002,2020705358
10069905,7/31/2002,2020705358
10084469,4/22/2002,0020457544
10084469,4/22/2002,0020457544
10084469,4/22/2002,0020457544
Column2:
SALEINFO
380214 196145 0000
380214 197183 0000
19-03-201-004
19-03-201-047
19-03-201-049
So I have one ID column and one SALEINFO column. I should have one SALEINFO record for one ID record. But at the moment, I have more than one SALEINFO record for one ID record.
My goal is to consolidate the records in SALEINFO column that belongs to one ID record.
In other words I want to see a result like this:
Column1:
ID
10069905,7/31/2002,2020705358
10084469,4/22/2002,0020457544
Column2:
SALEINFO
380214 196145 0000, 380214 197183 0000
19-03-201-004, 19-03-201-047, 19-03-201-049
Note: I have 2 to 19 SALEINFO records for one ID record. So the number of records in SALEINFO varies.
Thank you for your help.
Alex.
March 5, 2003 at 12:41 am
I know it's primitive, but it works.
set nocount on
go
create table Ttest (id char(29) not null, info varchar(20) not null)
go
insert into Ttest values('10069905,7/31/2002,2020705358','380214 196145 0000')
go
insert into Ttest values('10069905,7/31/2002,2020705358','380214 197183 0000')
go
insert into Ttest values('10084469,4/22/2002,0020457544','19-03-201-004')
go
insert into Ttest values('10084469,4/22/2002,0020457544','19-03-201-047')
go
insert into Ttest values('10084469,4/22/2002,0020457544','19-03-201-049')
go
create table ##TmpTest(id char(29), Allinfo varchar(4000) not null)
go
insert into ##TmpTest
select id, min('') from Ttest
group by id
go
declare @id as char(29)
declare @OldId as char(29)
declare @info as varchar(20)
declare @Comma as varchar(1)
set @OldId = ''
declare csrWRK CURSOR LOCAL FORWARD_ONLY for
select id, info
from Ttest
group by id, info
order by id, info
for read only
open csrWRK
FETCH NEXT FROM csrWRK INTO @id, @info
WHILE @@FETCH_STATUS = 0
BEGIN
if @OldId = @id
begin
set @comma = ','
end
else
begin
set @OldId = @id
set @comma = ''
end
update ##TmpTest
set Allinfo = Allinfo + @comma + @info
where ##TmpTest.id = @id
FETCH NEXT FROM csrWRK INTO @id, @info
END
-- Cursor afsluiten
CLOSE csrWRK
DEALLOCATE csrWRK
select * from ##TmpTest
go
drop table ##TmpTest
go
drop table Ttest
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 5, 2003 at 3:39 pm
Here is another way to use a pivot table query. This example is a modified version of one of the pivot table examples on my website:
create table Ttest (id char(29) not null, info varchar(20) not null)
go
insert into Ttest values('10069905,7/31/2002,2020705358','380214 196145 0000')
go
insert into Ttest values('10069905,7/31/2002,2020705358','380214 197183 0000')
go
insert into Ttest values('10084469,4/22/2002,0020457544','19-03-201-004')
go
insert into Ttest values('10084469,4/22/2002,0020457544','19-03-201-047')
go
insert into Ttest values('10084469,4/22/2002,0020457544','19-03-201-049')
go
-- declare variables
declare @p char(1000)
declare @i char(29)
declare @cnt int
declare @id char(29)
-- Print Report Heading
print 'id ' + ' AllInfo'
print '----------------------------- ' + '------------------------------------------'
set @p = ''
-- get set of distinct ids
declare ids cursor for
select distinct id from Ttest
OPEN ids
FETCH NEXT FROM ids
INTO @id
-- Process until no more items
WHILE @@FETCH_STATUS = 0
begin
-- string together all items with a comma between
select @i = id, @p = rtrim(@p) + ', '+ info
from Ttest a
where id = @id
-- print detail row
print @i + ' ' + rtrim(substring(@p,3,len(@p)))
-- next id
FETCH NEXT FROM ids
INTO @id
-- reset @P
set @p = ''
END
CLOSE ids
DEALLOCATE ids
-- remove example table
drop table Ttest
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
March 7, 2003 at 10:02 am
Thanks very much for your help.
With the samples you've posted I was able to get the results I needed.
You guys are great.
Alex.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply