Sorting and categorising data from database

Question

I am in need of a way to categories big amount (roughly 120 or more values) of data coming from a database. Nothing much comes to mind so I have decided to ask here. I would need a C# or SQL solution (or at least tips on it).

Data that needs to be sorted currently is in one column, but I am open to any suggestions. An example of the data is below:

- A2-11 (Main branch that contains all A2 from below)
- A2-113
- A2-114
- A2-115
- .
- .
- .
- F-L-5 (Main branch)
- F-L-55 (Another branch in there)
- F-L-56 (Contains all values below)
- F-L-566 
- F-L-567
- .
- .
- .

Thanks in Advance!!

Could be displayed as an example, small tree diagram:

TreeExample

Tag your question with the database you are using. – Gordon Linoff May 06 '17 at 17:18 — Gordon Linoff, May 06 '17 at 17:18

score 0 · Answer 1 · answered May 06 '17 at 17:21

0

Perhaps use a Hierarchyid? David

answered May 06 '17 at 17:21

David Browne - Microsoft

80,331
6
39
67

jdweng · Accepted Answer · 2017-05-08T23:24:52.540

Try custom OrderBy

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ConsoleApplication50
{
    class Program
    {

        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("Category", typeof(string));


            string[] data =  { "Main-A2-11", "Main-A2-113", "Main-A2-114", "Main-A2-115",
                              "Third-F-L-56", "Third-F-L-566", "Third-F-L-567",
                              "Second-F-L-5", "Second-F-L-55"
                            };

            foreach (string d in data)
            {
                dt.Rows.Add(new object[] { d });
            }

            dt = dt.AsEnumerable().GroupBy(x => x.Field<string>("Category").Contains("-") ? 
                     x.Field<string>("Category").Substring(0,x.Field<string>("Category").IndexOf("-")) :
                     x.Field<string>("Category"))
                .Select(x => x.OrderBy(y => new SortRecord(y,"Category"))).SelectMany(x => x).CopyToDataTable();

        }
    }
    public class SortRecord : IComparer<SortRecord >, IComparable<SortRecord>
    {
        string[] rowSplitArray;
        int length = 0;
        public SortRecord(DataRow x, string name)
        {
            rowSplitArray = x.Field<string>(name).Split(new char[] { '-' });
            length = rowSplitArray.Length;
        }


        public int Compare(SortRecord  rowA, SortRecord rowB)
        {

            int len = Math.Min(rowA.length, rowB.length);

            for (int i = 0; i < len; i++)
            {
                if (rowA.rowSplitArray[i] != rowB.rowSplitArray[i])
                    return rowA.rowSplitArray[i].CompareTo(rowB.rowSplitArray[i]);
            }

            return rowA.length.CompareTo(rowB.length);

        }
        public int CompareTo(SortRecord row)
        {
            return Compare(this, row);
        }
    }

}

I have just realised that it doesn't completely answer my question. Your solution sorts the data, however, I also want to categorise it. My data will not have "Main", "Second", nor anything else, but the format given : - F-L-56; - F-L-566; - F-L-567; How could I use what you have already written (@jdweng) and categorise in a way where the main branch would hold F-L-56, which holds F-L-566 and F-L-567. Could visualise it as sub menus in a menu. — Head, May 08 '17 at 15:52
I was expecting you to make this additional request. Already had the answer. Updated code. — jdweng, May 08 '17 at 23:25

score 0 · Answer 3 · edited May 23 '17 at 12:10

0

There are multiple way to achieve the result but if you need to decide the performance as well then follow this article.What are the options for storing hierarchical data in a relational database?

you can certainly use hierarchyid in sql. https://learn.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server

This might help i guess.

edited May 23 '17 at 12:10

Community

1
1

answered May 06 '17 at 18:31

Biswabid

1,378
11
26

Sorting and categorising data from database

3 Answers3