July 22, 2003 at 12:35 pm
Is there any way to write a SQL statement that will create a view out of Table 1 below and make the view look like Table 2?
Table 1 contains:
Col1 Col2
A "hello"
A "there"
B "Suzie"
B "Q"
C "another row"
D "more rows"
Table 2
Col1 Col2
A "hello there"
B "Suzie Q"
C "another row"
D "more rows"
I am currently using a cursor to do this but I need to optimize the sproc and make it faster. Is there a SQL statement that will make Table 1 turn into Table 2?
July 23, 2003 at 3:27 pm
I figured it out using a couple of temp tables and while loops.
If exists (select * from tempdb.dbo.sysobjects where name = '##tmp01')
begin
drop table ##tmp01
end
If exists (select * from tempdb.dbo.sysobjects where name = '##tmp02')
begin
drop table ##tmp02
end
select distinct Col1,space(500) as customerlist into ##tmp01 from Table1
select Col1, Col2 into ##tmp02 from Table1 order by Col1, Col2
declare @id int
declare @maxid int
declare @custcount int
declare @custctr int
declare @cust nvarchar(20)
declare @custlist nvarchar(500)
set @id = (select min(Col1) from Table1) set @maxid = (select max(Col1) from Table1)
while @id <= @maxid
begin
set @custcount = (select count(Col2)from Table1 where Col1 = @id)
set @custctr = 1 set @custlist = '' set @cust = ''
while @custctr <= @custcount
begin
set @custlist = @custlist + ',"' + (select top 1 Col2 from ##tmp02 where id = @id and Col2 > @cust) + '"'
set @cust = (select top 1 Col2 from ##tmp02 where Col1 = @id and Col2 > @cust)
set @custctr = @custctr + 1
end
update ##tmp01
set customerlist = substring(@custlist,2,499) where Col1 = @id
set @id = (select min(Col1) from ##tmp01 where Col1 > @id)
end
select * from ##tmp01
July 24, 2003 at 2:51 am
If you want a single row at a time you can do this:
declare @string varchar(255), @col1_value varchar(25)
set @string = ''
set @col1_value = 'A'
select @string = @string + col2 + ' '
from table_1
where col1 = @col1_value
print @string
If you want multiple rows, you might want to put the code into a User Defined Function (pass in Col1, return denormalised string) and then do something like this:
select distinct col1, dbo.fn_Denormalise(col1)
from table_1
I'm not sure whether this would be any quicker than your coding but it is an alternative.
Jeremy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply