SQL Server 2008R2 from Table to edges for Graph -

i have stumbled upon interesting challenge. have data in sql server table following format/content.

date     | name ---------+--------- 1/1/2010 | john 1/1/2010 | mark 1/1/2010 | peter 1/1/2010 | mia 2/4/2010 | john 2/4/2010 | billy 

i trying convert table file containing edges of graph.

i'll need edges file have columns , combinations table shows.

john | mark john | peter john | mia mark | mia mark | peter peter | mia john | billy 

i suspect part of can achieved pivot/unpivot don't know how proceed limiting pivot 2 columns.

also, don't know how make sure possible combinations of nodes, see first 4 'nodes' need become 6 'edges.'

you use row_number , "triangle join":

with cte (   select *, rn = row_number() over(partition date order name)   tab ) select c.name, c2.name cte c join cte c2   on c.date = c2.date   , c.rn < c2.rn; 

livedemo

output:

╔═══════╦═══════╗ ║ name  ║ name  ║ ╠═══════╬═══════╣ ║ john  ║ mark  ║ ║ john  ║ mia   ║ ║ john  ║ peter ║ ║ mark  ║ mia   ║ ║ mark  ║ peter ║ ║ mia   ║ peter ║      -- order based on `name` ║ billy ║ john  ║      -- same here `b` before `j` ╚═══════╩═══════╝ 

note:

to stable sort need add column indicate order within group same date. used name swaps names in last 2 rows.

---

version id column:

create table tab(id int identity(1,1)                  ,date date  not null                   ,name varchar(6) not null);  insert tab(date,name)  values ('1/1/2010','john'), ('1/1/2010','mark'), ('1/1/2010','peter')       ,('1/1/2010','mia'), ('2/4/2010','john'),('2/4/2010','billy');  cte (   select *, rn = row_number() over(partition date order id)   tab ) select c.name, c2.name cte c join cte c2   on c.date = c2.date   , c.rn < c2.rn; 

livedemo 2

output:

╔═══════╦═══════╗ ║ name  ║ name  ║ ╠═══════╬═══════╣ ║ john  ║ mark  ║ ║ john  ║ peter ║ ║ john  ║ mia   ║ ║ mark  ║ peter ║ ║ mark  ║ mia   ║ ║ peter ║ mia   ║ ║ john  ║ billy ║ ╚═══════╩═══════╝