March 28, 2003 at 2:10 pm
I wrote a VB algorithm to do the following task but I wonder if it could be done in t-sql (I don't think so, but everytime I've thought that I've lived to regret it).
We have 3 columns of strings. Column 1 is sorted (ascending) first. Column 2 is sorted for a given value in column 1.
ab1
ac2
ac3
db1
dc2
Here's the end result I want:
ab1
c2
3
db1
d c2
If a duplicate value occurs in the same colmun of two consecutive rows then replace the value in rows after the first with a blank or NULL.
In the example, since "a" occurs in both row 1 and row 2, column 1, replace the "a" in row 2 column 1 with a blank. After doing that we have
ab1
c2
ac3
db1
dc2
But row 3, column 1 is also an "a" so replacing it by a blank gives:
ab1
c2
c3
db1
dc2
Next look at column 2, rows 1 and 2. They are different so we move on to the next row. Now column 2, rows 2 and
3 are identical (="c") so the "c" in row 3 is replaced with a blank.
ab1
c2
3
db1
dc2
The pattern continues...until we reach the end result.
TIA,
For your time!
March 29, 2003 at 8:34 am
Post your actual code to do this in VB, and I'll write it in T-sql for ya....and yes, it can be done in T-sql.
Edited by - scorpion_66 on 03/29/2003 08:34:43 AM
March 31, 2003 at 2:12 am
It's certainly possible in TSQL, you just need to loop through the table replacing the duplicate values.
In the following example the values are inserted into another temp table (to set an identity field) before being returned:
This is just one example way of doing it, I'm sure there are others (eg: using cursors), but it should give you the idea.
-- Create table to hold original values
declare @T table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
-- populate
insert into @T values('A','B','1')
insert into @T values('A','C','2')
insert into @T values('A','C','3')
insert into @T values('D','B','1')
insert into @T values('D','C','2')
-- Create temp table to hold identity along side values for "While" code
declare @Temp table
(
Ident int identity(1,1),
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
-- populate Temp table
insert into @Temp
select * from @T
-- create table to hold end results
declare @Result table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
-- declare variables for the "while" clause
declare @Counter int
declare @max-2 int
declare @Col1 varchar(10)
declare @Col2 varchar(10)
declare @Col3 varchar(10)
set @Counter = 1
set @max-2 = (select max(ident) from @Temp)
-- start cycle
while @Counter <= @max-2
begin
if (select count(*) from @Result) = 0
begin
insert into @Result
select col1,col2,col3 from @Temp where Ident = @Counter
end
else
begin
-- col1
if (select col1 from @Temp where Ident = @Counter - 1)
=
(select col1 from @Temp where Ident = @Counter)
begin
set @Col1 = null
end
else
begin
set @Col1 = (select col1 from @Temp where Ident = @Counter)
end
-- col2
if (select col2 from @Temp where Ident = @Counter - 1)
=
(select col2 from @Temp where Ident = @Counter)
begin
set @Col2 = null
end
else
begin
set @Col2 = (select col2 from @Temp where Ident = @Counter)
end
-- col3
set @Col3 = (select col3 from @Temp where Ident = @counter)
insert into @Result
values (@Col1, @Col2, @Col3)
end
set @Counter = @Counter + 1
end
-- return results
select * from @Result
March 31, 2003 at 8:55 am
quote:
It's certainly possible in TSQL, you just need to loop through the table replacing the duplicate values.In the following example the values are inserted into another temp table (to set an identity field) before being returned:
This is just one example way of doing it, I'm sure there are others (eg: using cursors), but it should give you the idea.
-- Create table to hold original values
declare @T table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
-- populate
insert into @T values('A','B','1')
insert into @T values('A','C','2')
insert into @T values('A','C','3')
insert into @T values('D','B','1')
insert into @T values('D','C','2')
-- Create temp table to hold identity along side values for "While" code
declare @Temp table
(
Ident int identity(1,1),
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
-- populate Temp table
insert into @Temp
select * from @T
-- create table to hold end results
declare @Result table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
-- declare variables for the "while" clause
declare @Counter int
declare @max-2 int
declare @Col1 varchar(10)
declare @Col2 varchar(10)
declare @Col3 varchar(10)
set @Counter = 1
set @max-2 = (select max(ident) from @Temp)
-- start cycle
while @Counter <= @max-2
begin
if (select count(*) from @Result) = 0
begin
insert into @Result
select col1,col2,col3 from @Temp where Ident = @Counter
end
else
begin
-- col1
if (select col1 from @Temp where Ident = @Counter - 1)
=
(select col1 from @Temp where Ident = @Counter)
begin
set @Col1 = null
end
else
begin
set @Col1 = (select col1 from @Temp where Ident = @Counter)
end
-- col2
if (select col2 from @Temp where Ident = @Counter - 1)
=
(select col2 from @Temp where Ident = @Counter)
begin
set @Col2 = null
end
else
begin
set @Col2 = (select col2 from @Temp where Ident = @Counter)
end
-- col3
set @Col3 = (select col3 from @Temp where Ident = @counter)
insert into @Result
values (@Col1, @Col2, @Col3)
end
set @Counter = @Counter + 1
end
-- return results
select * from @Result
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 31, 2003 at 8:56 am
Here is a way to do this with a create and update statement against a temp table.
-- Create table to hold original values
declare @T table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
-- populate
insert into @T values('A','B','1')
insert into @T values('A','C','2')
insert into @T values('A','C','3')
insert into @T values('D','B','1')
insert into @T values('D','C','2')
select * into #t from @T
declare @col1 varchar(100)
declare @col2 varchar(100)
declare @col3 varchar(100)
set @col1 = char(1)
set @col2 = char(1)
update #T
set
col1 = case when col1 <> substring(right(rtrim(@col1),2),1,1) then
right(rtrim(@col1),1)
else ' ' end,
@col1 = rtrim(@col1) + col1,
col2 = case when col2 <> substring(right(rtrim(@col2),2),1,1) then
right(rtrim(@col2),1)
else ' ' end,
@col2 = rtrim(@col2) + col2
select * from #t
drop table #T
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply