Rearrange DB query result in array for duplicate rows

Question

I want to rearrange query result from db with duplicate data into distinct row (for data display )but having 1 column collect related values in comma separated if more than 1 item found.

For example consider list below (from db query):

ID  GROUP   DATE
A1212   1   1/1/2019
A1212   2   1/1/2019
A1313   1   3/1/2019

into:

ID  GROUP
A1212   1,2
A1313   1

sql string:

select DISTINCT Declaration_Form_Master.*,Declaration_Form_Detail.KPA_Group, Temp_Storage_Reg_V.*,Temp_Storage_Reg_V.ID as TSEID,Users.Company As Uc, 'NULL' as 'CTU_id' from Declaration_Form_Master left outer join Temp_Storage_Reg_V on (Declaration_Form_Master.Master_ID=Temp_Storage_Reg_V.TSEMaster_ID) right join Users on (Declaration_Form_Master.Agent_ID = Users.UserId) right join Declaration_Form_Detail on (Declaration_Form_Master.Master_ID = Declaration_Form_Detail.Master_ID) where Declaration_Form_Master.Confirmation ='1' and Submit_Date >= '2019-01-28' and Submit_Date < '2019-08-30' order by Application_ID DESC

need to join all table because search criteria based on column on multiple table. i cant figure out on sql, but want to rearrange back the result using array or list.

Maybe some algorithm can help.

Show us how your c# code and what it looks like. How are you grabbing this data? Are you using Linq or are you brute forcing this? — Train
– Train, Commented Aug 28, 2019 at 15:41
if you are using linq: try to group your data by id, then output grp.Key into first column and string.Join all group values into the second column — Michael
– Michael, Commented Aug 28, 2019 at 15:46
You have to show us your c# code, or if you want to do this in SQL show us what you have tried. — Train
– Train, Commented Aug 28, 2019 at 15:51
im using legacy massive ORM. i know LINQ can do this. just update my question to include sql from code. — syahman
– syahman, Commented Aug 28, 2019 at 15:57

jdweng · Accepted Answer · 2019-08-28 15:55:01Z

1

Try following :

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

using System.Data;

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(string));
            dt.Columns.Add("GROUP", typeof(int));
            dt.Columns.Add("DATE", typeof(DateTime));

            dt.Rows.Add(new object[] {"A1212", 1, DateTime.Parse("1/1/2019")});
            dt.Rows.Add(new object[] {"A1212", 2, DateTime.Parse("1/1/2019")});
            dt.Rows.Add(new object[] {"A1213", 1, DateTime.Parse("3/1/2019")});


            DataTable dt2 = new DataTable();
            dt2.Columns.Add("ID", typeof(string));
            dt2.Columns.Add("GROUP", typeof(string));

            var groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("ID")).ToList();

            foreach (var group in groups)
            {
                dt2.Rows.Add(new object[] {
                    group.Key,
                    string.Join(",", group.Select(x => x.Field<int>("GROUP").ToString()).Distinct())
                });
            }

        }
    }

}

answered Aug 28, 2019 at 15:55

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

syahman Over a year ago

ok, will try this. need to pass query result to datatable 1st.

jdweng Over a year ago

Your query should return already a list of rows which is equivalent of dt.AsEnumerable()

syahman Over a year ago

var group = data.GroupBy.Field<Int32>("Application_ID").ToList(); Exception Details: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'object' does not contain a definition for 'GroupBy'

jdweng Over a year ago

DataTable dt = query.CopyToDataTable();

Tohm · Accepted Answer · 2019-08-29 14:34:51Z

1

If you want to do this action even in T-SQL you must aggregate the result By exemple

DECLARE @fakeTable TABLE (ID varchar(50), [GROUP] int)

INSERT INTO @fakeTable 
VALUES
('A1212',   1 ),
('A1212',   2 ),
('A1213',   1 )

SELECT
    ID,
    [GROUP] = stuff((
        SELECT ','+convert(varchar(100),[GROUP])
        FROM @fakeTable innerTable
        WHERE innerTable.ID = orgTable.ID
        ORDER BY [GROUP]
        for xml path (''), type).value('.','nvarchar(max)')

      ,1,1,'')
FROM (
SELECT DISTINCT ID FROM @fakeTable
) orgTable

More info about aggregation

answered Aug 29, 2019 at 14:34

Tohm

3051 silver badge5 bronze badges

Comments

syahman · Accepted Answer · 2019-08-29 14:16:20Z

DataTable dx = new DataTable();
    dx.Columns.Add("Application_ID", typeof(int));
    dx.Columns.Add("KPA_Group", typeof(int));

    foreach(var z in data) { 
    dx.Rows.Add(new object[] { z.Application_ID,z.KPA_Group });
    }

    DataTable dt1 = new DataTable();
    dt1.Columns.Add("Application_ID", typeof(string));
    dt1.Columns.Add("KPA_Group", typeof(string));

    var groups = dx.AsEnumerable().GroupBy(x => x.Field<Int32>("Application_ID")).ToList();


    foreach (var group in groups)
    {
        dt1.Rows.Add(new object[] {
                    group.Key,
                    string.Join(",", group.Select(x => x.Field<int>("KPA_Group").ToString()).Distinct())
                });
    }


    foreach (DataRow row in dt1.Rows)
    {

        @:@row["Application_ID"].ToString() | 
        @:@row["KPA_Group"] <br />
    }

result :

2019080001 | 2,3 
2019070002 | 2 
2019070001 | 2 
2019060001 | 1,2 
2019040002 | 2 
2019030002 | 2 
2019030001 | 2 
2019020002 | 2 
2019020001 | 2

i dont understand why i can't use CopyToDataTable method. solved this by using foreach.

Collectives™ on Stack Overflow

Rearrange DB query result in array for duplicate rows

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related