Sort linked list
-
Hello all,
I almost have my first T-SQL project finished. And believe me for a NON_SQL programmer, it's pretty hard,
I am now stuck at the end of my project. I have the following table:
create table x ( id1 INT, id2 INT )
insert x (id1,id2) values (1, 5)
insert x (id1,id2) values (1, 8)
insert x (id1,id2) values (1, 10)
insert x (id1,id2) values (2, 4)
insert x (id1,id2) values (5, 1)
insert x (id1,id2) values (7, 10)
insert x (id1,id2) values (8, 1)
insert x (id1,id2) values (10, 1)
insert x (id1,id2) values (10, 7)
go
This shows connections between rows.
It means that rows 1,5,8 and 10 have a relation, also rows 2 and 4 and rows 7 and 10
I want to transform this table into a new table that has one VARCHAR column.
It should then look like:
resulttable (one VARCHAR column):
'1,5,8,10'
'2,4'
'7,10'
I do this now by a normal C program. I export the table as ASCII. However I would love to have this done in T-SQL. But I have no idea how to start. In my produktion environment this table x will contain about 50.000 rows.
Thanks in advance!
Erik
-
Here's one crazy technique that will work for your example...
If it doesn't work for your actual situation, please adjust your example to illustrate the issue.
--data
if object_id('tempdb.dbo.#x') is not null drop table #x
create table #x ( id1 INT, id2 INT, s varchar(100) )
create unique clustered index xIdx on #x (id1, id2)
insert #x (id1,id2) values (1, 5)
insert #x (id1,id2) values (1, 8)
insert #x (id1,id2) values (1, 10)
insert #x (id1,id2) values (2, 4)
insert #x (id1,id2) values (5, 1)
insert #x (id1,id2) values (7, 10)
insert #x (id1,id2) values (8, 1)
insert #x (id1,id2) values (10, 1)
insert #x (id1,id2) values (10, 7)
--calculation
declare @s-2 varchar(100)
declare @previous_id1 int
set @s-2 = ''
update #x set
@s-2 = case when id1 = @previous_id1 then @s-2 else cast(id1 as varchar(5)) end
+ ',' + cast(id2 as varchar(5)),
s = @s-2,
@previous_id1 = id1
where id1 < id2
select s from #x a where id1 < id2 and len(s) = (select max(len(s)) from #x where id1 = a.id1)
You might well be better off leaving it in C though. Also, note that I've just ignored any row where id2 >= id1 because they seem redundant to do what you need (in this example).
Hope this helps
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
And here is the csv string method
create function f_csv(@id1 int)
returns varchar(100)
as
begin
declare @csv varchar(100)
select @csv = convert(varchar(10), @id1)
select@csv = @csv + ',' + convert(varchar(10), id2)
fromx
whereid1= @id1
order by id2
return @csv
end
selectdbo.f_csv(id1)
fromx
group by id1
-
Hello Ryan,
Thanks for your answer. I have real problems in deciding when to use T-SQL or when to use another programming language.
The problem I am solving is the following:
CREATE TABLE [dbo].[companies](
[id] [int] NOT NULL,
[name] [varchar](200))
INSERT
companies (id, name) VALUES (1,'Erik United Company')
INSERT companies (id, name) VALUES (2,'Super Grocery Store')
INSERT companies (id, name) VALUES (3,'Harrods')
INSERT companies (id, name) VALUES (4,'Erik United Comp.')
INSERT companies (id, name) VALUES (5,'Super Fictious Ltd.')
INSERT companies (id, name) VALUES (6,'Erik United')
INSERT companies (id, name) VALUES (7,'Super Grocery')
GO
-- What I want to do is to get a list of lines that represent the same company (hopefully).
-- My approach was to create a new table, that contains all the separate words.
-- (see my question: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=275803)
-- So I have now the following table:
TABLE
x (int, varchar)
1, Erik
1, United
1, Company
2, Super
2, Grocery
2, Store
3, Harrods
4, Erik
4, United
4, Comp.
5, Super
5, Fictious
5, Ltd.
6, Erik
6, United
7, Super
7, Grocery
-- Now I compary every single word in the table with the rest.
-- If I find the same (or almost the same) words then I count them as equal
-- This generates the following list:
1, 4
1, 6
1, 4
1, 6
1, 6
2, 5
2, 7
2, 7
-- Now I want to take this list and present it in a readable way:
-- And this is the point I get stuck. So I handle the list in C.
-- But I would like it much more to get something in T-SQL that says:
table
equals (newid, varchar)
1, 'Erik United Company'
1, 'Erik United Comp.'
1, 'Erik United'
2, 'Super Grocery Store'
2, 'Super Grocery'
-- I probably take the wrong approach but maybe somebody has a better idea, how to find the equals??
Thanks
,
Erik
-
This is what I thought you are looking for then I saw the previous replies so I left it alone, to apply equality in SQL Server require case sensitive implementation. Try the links below for details. In C# .NET you must implement the ICOMPARER or ICOMPARABLE interfaces before you sort a list. Hope this helps.
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
http://msdn2.microsoft.com/en-us/library/ms132319.aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Dear Gift,
Thank you for your reply. I took a look at the links, but I don't see really how to use the sorted list, to get my 'fuzzy grouping' operational. I think you mean I should implement ICOMPARER with my own search routine. But how do I then use the sorted list?
I am sorry I don't get it immediatly...
Erik
-
In your original post you said you want to sort a Linked List so I gave you how to sort in both SQL Server using Binary Sort with T-SQL in the first link and C# in the second link which require the ICOMPARER or ICOMPARABLE interface. Take a look at an existing sorted list code in Codeproject. Now if you are looking for fuzzy grouping you, then it is not standard sort so in SQL Server you have to check CUBE abd ROLLUP operators. I am sorry if I did not understand what you are looking for. Hope this helps.
http://www.codeproject.com/cs/miscctrl/sortablelist.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply