Grouping data in SQL

Question

I am working on a small project using C # and EF5.0, and I need to group some data. Say I have a table of columns in a building, as shown below.

+----------+--------Columns Table--+------+------+ | ColumnID |ColumnName|Width|Length|Height|number| +----------+----------+-----+------+------+------+ | 1 | C101 | 50 | 70 | 250 | 1 | | 2 | C102 | 70 | 70 | 250 | 1 | | 3 | C103 | 70 | 60 | 250 | 1 | | 4 | C104 | 90 | 70 | 250 | 1 | | 5 | C105 | 40 | 50 | 250 | 1 | | 6 | C106 | 50 | 70 | 250 | 1 | | 7 | C107 | 50 | 60 | 250 | 1 | | 8 | C108 | 70 | 70 | 250 | 1 | +----------+----------+-----+------+------+------+ 

I need C # code to see the above data as follows:

 +----------+---Groupped Columns Table-----+------+ |G_ColumnID|ColumnName|Width|Length|Height|number| +----------+----------+-----+------+------+------+ | 1 |C(101-106)| 50 | 70 | 250 | 2 | | 2 |C(102-108)| 70 | 70 | 250 | 2 | | 3 | C103 | 70 | 60 | 250 | 1 | | 4 | C104 | 90 | 70 | 250 | 1 | | 5 | C105 | 40 | 50 | 250 | 1 | | 6 | C107 | 50 | 60 | 250 | 1 | +----------+----------+-----+------+------+------+ 

I prefer keys over the exact solution.

EDIT: Below is my current status. I think that with this code I can find columns with the same height, width and length. But I'm not sure how to create a new name for the group.

 using (pehlivanEntities context = new pehlivanEntities()) { foreach (var item in context.table1) { int id = item.ColumnID; foreach (var item2 in context.table1) { int id2 = item2.ColumnID; if (id != id2) { if (item.Width == item2.Width) { if (item.Length == item2.Length) { if (item.Height == item2.Height) { //Alter item.ColumnName //increase item.number by one //Remove item2 } } } } } } } 

Answer 1

Ok, you will start by grouping by composite key:

 var groups = myData.GroupBy(d => new{d.Width, d.Length, d.Height}) 

then

 groups .Select(g => new { g.Key.Width, g.Key.Length, g.Key.Height, columnNames = g.Select(x => x.ColumnName), number = g.Count()}) 

then some string manipulations in the columnNames field