May 18, 2010 at 4:30 am
In:
i
-------
1
2
3
4
Out:
i
-------
1
2
3
4
1,2
1,3
1,4
2,3
2,4
3,4
1,2,3
1,2,4
1,3,4
2,3,4
1,2,3,4
NULL
/*--------
declare @t table (i varchar(500), unique(i))
insert @t select 1 union select 2 union select select 3 union select 4
--------*/
May 18, 2010 at 5:04 am
Try this
WITH RecCTE AS
(
SELECT Convert(VarChar(30),i) i FROM T
UNION ALL
SELECT Convert(Varchar(30),t2.i+','+t3.i) as i FROM T T2
CROSS JOIN RecCTE T3
WHERE t2.i<>t3.i AND t2.i<LEFT(t3.i,1)
)
SELECT * FROM RecCTE
ORDER BY LEN(I),i
May 18, 2010 at 5:07 am
You probably wil have to use Paul White NZ's N-Way Handshaking code for your requirement!
NOTE : Jeff Moden had advised that this code contains triangular joins and we must use this carefully or rather refrain from it!
If it just a assignment,then this should work for you!
The real code:
DECLARE @People
TABLE (
person_id INTEGER IDENTITY(0,1) PRIMARY KEY,
name VARCHAR(10) NOT NULL
);
INSERT @People (name) VALUES ('Alan');
INSERT @People (name) VALUES ('Bob');
INSERT @People (name) VALUES ('Carol');
INSERT @People (name) VALUES ('Debra');
INSERT @People (name) VALUES ('Eric');
INSERT @People (name) VALUES ('Frank');
INSERT @People (name) VALUES ('Gina');
INSERT @People (name) VALUES ('Harry');
INSERT @People (name) VALUES ('Ian');
INSERT @People (name) VALUES ('Julie');
DECLARE @Bits
TABLE (
bit_id BIGINT NOT NULL PRIMARY KEY,
value BIGINT NOT NULL
);
INSERT @Bits (bit_id, value)
SELECT P.person_id - 1, POWER(2, (P.person_id - 1))
FROM @People P;
DECLARE @Grouped
TABLE (
N BIGINT NOT NULL,
name VARCHAR(10) NOT NULL,
group_size BIGINT NOT NULL,
group_order BIGINT NOT NULL,
group_row BIGINT NOT NULL,
UNIQUE (N, name),
UNIQUE (group_row, group_size, group_order, N)
);
INSERT @Grouped
(N, name, group_size, group_order, group_row)
SELECT Q.N,
Q.name,
Q.group_size,
group_order = DENSE_RANK() OVER (PARTITION BY Q.group_size ORDER BY Q.N),
group_row = DENSE_RANK() OVER (PARTITION BY Q.group_size ORDER BY Q.name)
FROM (
SELECT N.N,
P.name,
group_size = COUNT(*) OVER (PARTITION BY N.N)
FROM dbo.Numbers (POWER(2, (SELECT COUNT(*) FROM @People))) N
JOIN @Bits B
ON N.N & B.value = B.value
JOIN @People P
ON P.person_id = B.bit_id
) Q
SELECT names =
STUFF
(
(
SELECT ',' + name
FROM @Grouped T2
WHERE T2.N = T1.N
ORDER BY
name ASC
FOR XML PATH('')
)
, 1, 1, SPACE(0))
FROM @Grouped T1
WHERE group_row = 1
ORDER BY
T1.group_size,
T1.group_order;
GO
The code for Numbers Function in that:
DROP FUNCTION dbo.Numbers;
GO
CREATE FUNCTION dbo.Numbers
(@N BIGINT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH
N1 AS (SELECT N = 1 UNION ALL SELECT 1),
N2 AS (SELECT N = 1 FROM N1 T, N1),
N3 AS (SELECT N = 1 FROM N2 T, N2),
N4 AS (SELECT N = 1 FROM N3 T, N3),
N5 AS (SELECT N = 1 FROM N4 T, N4),
N6 AS (SELECT N = 1 FROM N5 T, N5),
NM AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N FROM N6)
SELECT TOP (@N)
N
FROM NM
WHERE N <= @N
ORDER BY
N ASC;
GO
Hope this helps you! But i am expecting Jeff to comment on this!
😎
May 18, 2010 at 5:11 am
Good code , Gopi! 😎
May 18, 2010 at 5:27 am
Thank you:-)
May 18, 2010 at 6:12 am
ColdCoffee (5/18/2010)
You probably wil have to use Paul White NZ's N-Way Handshaking code for your requirement!NOTE : Jeff Moden had advised that this code contains triangular joins and we must use this carefully or rather refrain from it!
If it just a assignment,then this should work for you!
The real code:
DECLARE @People
TABLE (
person_id INTEGER IDENTITY(0,1) PRIMARY KEY,
name VARCHAR(10) NOT NULL
);
INSERT @People (name) VALUES ('Alan');
INSERT @People (name) VALUES ('Bob');
INSERT @People (name) VALUES ('Carol');
INSERT @People (name) VALUES ('Debra');
INSERT @People (name) VALUES ('Eric');
INSERT @People (name) VALUES ('Frank');
INSERT @People (name) VALUES ('Gina');
INSERT @People (name) VALUES ('Harry');
INSERT @People (name) VALUES ('Ian');
INSERT @People (name) VALUES ('Julie');
DECLARE @Bits
TABLE (
bit_id BIGINT NOT NULL PRIMARY KEY,
value BIGINT NOT NULL
);
INSERT @Bits (bit_id, value)
SELECT P.person_id - 1, POWER(2, (P.person_id - 1))
FROM @People P;
DECLARE @Grouped
TABLE (
N BIGINT NOT NULL,
name VARCHAR(10) NOT NULL,
group_size BIGINT NOT NULL,
group_order BIGINT NOT NULL,
group_row BIGINT NOT NULL,
UNIQUE (N, name),
UNIQUE (group_row, group_size, group_order, N)
);
INSERT @Grouped
(N, name, group_size, group_order, group_row)
SELECT Q.N,
Q.name,
Q.group_size,
group_order = DENSE_RANK() OVER (PARTITION BY Q.group_size ORDER BY Q.N),
group_row = DENSE_RANK() OVER (PARTITION BY Q.group_size ORDER BY Q.name)
FROM (
SELECT N.N,
P.name,
group_size = COUNT(*) OVER (PARTITION BY N.N)
FROM dbo.Numbers (POWER(2, (SELECT COUNT(*) FROM @People))) N
JOIN @Bits B
ON N.N & B.value = B.value
JOIN @People P
ON P.person_id = B.bit_id
) Q
SELECT names =
STUFF
(
(
SELECT ',' + name
FROM @Grouped T2
WHERE T2.N = T1.N
ORDER BY
name ASC
FOR XML PATH('')
)
, 1, 1, SPACE(0))
FROM @Grouped T1
WHERE group_row = 1
ORDER BY
T1.group_size,
T1.group_order;
GO
The code for Numbers Function in that:
DROP FUNCTION dbo.Numbers;GO
CREATE FUNCTION dbo.Numbers
(@N BIGINT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH
N1 AS (SELECT N = 1 UNION ALL SELECT 1),
N2 AS (SELECT N = 1 FROM N1 T, N1),
N3 AS (SELECT N = 1 FROM N2 T, N2),
N4 AS (SELECT N = 1 FROM N3 T, N3),
N5 AS (SELECT N = 1 FROM N4 T, N4),
N6 AS (SELECT N = 1 FROM N5 T, N5),
NM AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N FROM N6)
SELECT TOP (@N)
N
FROM NM
WHERE N <= @N
ORDER BY
N ASC;
GO
Hope this helps you! But i am expecting Jeff to comment on this!
😎
coldcoffee
N-Way Handshaking code ???
i am just hearing this word first time. what it is?
karthik
May 18, 2010 at 6:21 am
I can put N-Way handshaking as this:
Assume there are 4 people sitting in the room and assume they are to first hand-shake with each other, then they will pair up in set of 2's and start hand shaking other individuals, then they pair up in set of 3's and hand-shake and so on.. it is actually finding the different combinations of persons handshaking.
As for the name,it is just a mere name that was used in that thread, thats al.. its not a predefned phrase or method 😀
hope this info helps you!
May 18, 2010 at 9:40 am
Gopi Muluka (5/18/2010)
Try this
WITH RecCTE AS(
SELECT Convert(VarChar(30),i) i FROM T
UNION ALL
SELECT Convert(Varchar(30),t2.i+','+t3.i) as i FROM T T2
CROSS JOIN RecCTE T3
WHERE t2.i<>t3.i AND t2.i<LEFT(t3.i,1)
)
SELECT * FROM RecCTE
ORDER BY LEN(I),i
Thanks. It seems like run like a magic! (I need to spend enough time to find the logic behind the code)
Also thanks ColdCoffee for the CODE, I will spend time on it.
June 21, 2010 at 3:25 am
The solution (at the second post in this topic) will not word when my sample data be like following, and my desire result is:
Declare @t table (id int, score int)
insert @t select 1, 1
union select 20, 3
union select 300, 4
union select 4000, 2;
select * from @t
/*
id score
----------- -----------
1 1
20 3
300 4
4000 2
*/
/*
elements sum(score)
------------ ------------
1 1
20 3
300
4000
1,20 4
1,300
1,4000
20,300
20,4000
300,4000
...
...
1,20,300,400 10
June 21, 2010 at 4:03 am
Okay I find the mistake and fix it
Here the solution:
Declare @t table (id int, score int)
insert @t select 1, 1
union select 20, 3
union select 300, 4
union select 4000, 2;
select * from @t
/*
id score
----------- -----------
1 1
20 3
300 4
4000 2
*/
;WITH RecCTE AS
(
SELECT Convert(VarChar(30),id) id, score
FROM @T
UNION ALL
SELECT Convert(Varchar(30),cast(t2.id as varchar(10)) + ',' + cast(t3.id as varchar(10))) as id, t2.score + t3.score as score
FROM @T T2
CROSS JOIN RecCTE T3
WHERE cast(t2.id as varchar(10)) <> t3.id
AND t2.id < LEFT(t3.id,charindex(',',t3.id+',')-1)
)
SELECT * FROM RecCTE
ORDER BY id,LEN(id)
/*
id score
------------------------------ -----------
1 1
1,20 4
1,20,300 8
1,20,300,400 10
1,20,4000 6
1,300 5
1,300,4000 7
1,4000 3
20 3
20,300 7
20,300,4000 9
20,4000 5
300 4
300,4000 6
4000 2
*/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply