November 10, 2009 at 10:16 pm
hi, I want to query a table with columns : ID , field1, field2 and use group by statement to group records using field1 and field2 in a combinational way that the two following records palce in a same group:
Record1 : id1 , value1, value2
Record2 : id2 , value2, value1
Is it possible? how?
Thanks in advance for any hep.
November 11, 2009 at 3:12 am
I’m sorry but it is hard to understand what you need. Can you post a small script that includes create table statement that creates the table, insert statements to insert demo data and then explain what is the data that you expect to get back?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 11, 2009 at 12:55 pm
group by isnull(field1,'') + isnull(field2,'')
November 11, 2009 at 11:30 pm
hi again, sorry if i couldn't properly present my problem. in detail there are two tables , one named 'Trunks' with columns [node 1] and [noede 2] and another one named 'free capacity' that has columns [trunk id] and [free]. i want to join the two table on [trunk id] that some records of this join are:
[trunk ID] [Node1] [Node2] [Free]
37 51162 51181 0
64 51181 51162 5
73 51121 51162 16
85 51162 51121 1
88 51170 51121 2
103 51150 51160 16
and then goup them in a way that trunk 37 and 64 be placed in the same group because their nodes are the same though in different fields, that is they both have 51162 and 51181 in their nodes. and just the same condition for trunk 73 and 85 ,... .
What i want out of grouping is:
Node 1 Node 2 sum(Free)
51181 51162 5
51121 51162 17
51170 51121 2
51150 51160 16
i tried to write a function which got a unique id for trunks with the same nodes :
Create function [dbo].[GetUniqueID] (@node1 int, @node2 int)
returns nvarchar(50)
as
begin
declare @ret nvarchar(50);
declare @n1 nvarchar(50);
declare @n2 nvarchar(50)
set @n1 = convert(nvarchar(50), @node1);
set @n2 = convert(nvarchar(50), @node2);
if( @node1 > @node2)
set @ret = @n1 + '#' + @n2
else
set @ret = (@n2 + '#' + @n1 )
return @ret
end;
and use it in grouping but this is very limiting.
is there any answer for this problem?
of course I'm sure guys here can help;-)
thanks.
November 12, 2009 at 5:09 am
There are (of course) several ways to do this. One is to make sure that the lower node-number of the two always get in the first column. Then the grouping will work as you want:
create table #Trunks (
[trunk ID] int,
[Node1] int,
[Node2] int,
[Free] int
)
insert into #Trunks ([trunk ID], [Node1], [Node2], [Free]) VALUES (37, 51162, 51181, 0)
insert into #Trunks ([trunk ID], [Node1], [Node2], [Free]) VALUES (64, 51181, 51162, 5)
insert into #Trunks ([trunk ID], [Node1], [Node2], [Free]) VALUES (73, 51121, 51162, 16)
insert into #Trunks ([trunk ID], [Node1], [Node2], [Free]) VALUES (85, 51162, 51121, 1)
insert into #Trunks ([trunk ID], [Node1], [Node2], [Free]) VALUES (88, 51170, 51121, 2)
insert into #Trunks ([trunk ID], [Node1], [Node2], [Free]) VALUES (103, 51150, 51160, 16)
-- Used CASE to make sure the lower node-number is in the first column
-- and the higher in the second column...
select CASE WHEN Node1 > Node2 THEN Node2 ELSE Node1 END AS Node1,
CASE WHEN Node1 > Node2 THEN Node1 ELSE Node2 END AS Node2,
sum(Free) AS [sum(Free)]
from #Trunks
group by CASE WHEN Node1 > Node2 THEN Node2 ELSE Node1 END,
CASE WHEN Node1 > Node2 THEN Node1 ELSE Node2 END
Hope this helps!
/Markus
November 13, 2009 at 10:41 pm
thanks Markus, it works fine. hope this topic be useful for other friends here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply