"Pivoting" Rows to Columns in C#

We have a function that takes a dataset and "reorganizes" it from rows into columns:
private DataSet ReorgFileDataset(ref DataSet InDataSet)
{
	DataRow _ResultDataRow = new DataRow();
	DataRow _DataRow = new DataRow();
	DataSet _ResultDataSet = new DataSet();
	DataTable _ResultDataTable = new DataTable();
	_ResultDataSet.Tables.Add(_ResultDataTable);

	string ColumnName = null;
	string Value = null;
	int FileID = 0;

	//*********************************************************	
	// Builds the columns for each of the Keywords
	//*********************************************************
	foreach ( _DataRow in InDataSet.Tables(0).Rows) {
		ColumnName = _DataRow.Item("KEYWORD");
		if (!_ResultDataTable.Columns.Contains(ColumnName)) {
			_ResultDataTable.Columns.Add(ColumnName);
		}
	}
	_ResultDataTable.Columns.Add("DSENT");
	//*********************************************************

	_ResultDataRow = null;
	//*********************************************************
	// Now reads the data into the columns
	//*********************************************************
	foreach ( _DataRow in InDataSet.Tables(0).Rows) {
	    // Has to keep track of each "record", they all have same RID
	    // Creates a new row in _ResultDataTable when it changes
		if (FileID != _DataRow.Item("RID")) {
			if ((_ResultDataRow != null)) {
			    // Adds the previously created row
				_ResultDataTable.Rows.Add(_ResultDataRow);
			}
			FileID = _DataRow.Item("RID");
			// Creates the new row and sets the LASTMOD value from the DSENT value
			_ResultDataRow = _ResultDataTable.NewRow();
			_ResultDataRow.Item("DSENT") = _DataRow.Item("LASTMOD");
		}
		// Now read the data into the columns
		if (_DataRow.Item("KEYWORD") != DBNull & _DataRow.Item("VALUE") != DBNull) {
			ColumnName = _DataRow.Item("KEYWORD");
			Value = _DataRow.Item("VALUE");
			_ResultDataRow.Item(ColumnName) = Value;
		}
	}
	//*********************************************************
	if (_ResultDataRow != null) {
		_ResultDataTable.Rows.Add(_ResultDataRow);
	}
	return _ResultDataSet;
}

Open in new window

But I need it to create this JSON like this instead of a Dataset:
[
 {"CER":"5010243", "ST":"SC", "RID":"1088741", "DNAME":"CERT", "EC":"20051109SCIP", "FNAME":"\1088741\17\C\SC_20051109SCIP.PDF", "PDATE":"20051130", "DG":"", "PCODE":"", "UD":"", "DSENT":"20150911"}
 {"CER":"4925213", "ST":"SC", "RID":"1088741", "DNAME":"CERT", "EC":"20050421SCPA", "FNAME":"\1088741\17\C\SC_20050421SCPA.PDF", "PDATE":"20050509", "DG":"", "PCODE":"", "UD":"", "DSENT":"20150911"}
 {"CER":"", "ST":"", "RID":"1088741", "DNAME":"PASS LET", "EC":"20061102SCIP", "FNAME":"\1088741\17\L\CR_PASS_LET20061102SCIP.PDF", "PDATE":"20061116", "DG":"CR", "PCODE":"IP", "", "DSENT":"20150911"}
 {"CER":"", "ST":"", "RID":"1088741", "DNAME":"ID Card", "EC":"", "FNAME":"\1088741\17\LET\ID_CARD_1088741.PDF", "PDATE":"20150913", "DG":"", "PCODE":"", "UD":"", "DSENT":"20150911"}
 {"CER":"", "ST":"", "RID":"1088741", "DNAME":"UPD LET", "EC":"", "FNAME":"\1088741\17\L\UPD_LET_20150828SCLH.PDF", "PDATE":"20150911", "DG":"CR", "PCODE":"LH", "UD":"5704220", "DSENT":"20150911"}
]

Open in new window


Here's demo data, each record has a KEYWORD that are essentially the columns and the VALUE is the data for the column except the FNAME column gets the value of FNAME when the KEYWORD is FNAME. The code also adds a column called DSENT that contains the LASTMOD value from first "record" in the group.

"RID","KEYWORD","VALUE","FNAME","LASTMOD"
162895,"CER","5010243","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"ST","SC","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"RID","1088741","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"DNAME","CERT","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"EC","20051109SCIP","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"FNAME","\1088741\17\C\SC_20051109SCIP","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"PDATE","20051130","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
251893,"CER","4925213","\1088741\17\C\SC_20050421SCPA.PDF","4/25/2006 4:36:16 PM"
251893,"ST","SC","\1088741\17\C\SC_20050421SCPA.PDF","4/25/2006 4:36:16 PM"
251893,"RID","1088741","\1088741\17\C\SC_20050421SCPA.PDF","4/25/2006 4:36:16 PM"
251893,"DNAME","CERT","\1088741\17\C\SC_20050421SCPA.PDF","4/25/2006 4:36:16 PM"
251893,"EC","20050421SCPA","\1088741\17\C\SC_20050421SCPA.PDF","4/25/2006 4:36:16 PM"
251893,"FNAME","\1088741\17\C\SC_20050421SCPA","\1088741\17\C\SC_20050421SCPA.PDF","4/25/2006 4:36:16 PM"
251893,"PDATE","20050509","\1088741\17\C\SC_20050421SCPA.PDF","4/25/2006 4:36:16 PM"
414199,"RID","1088741","\1088741\17\L\CR_PASS_LET20061102SCIP.PDF","1/31/2007 1:20:46 PM"
414199,"DG","CR","\1088741\17\L\CR_PASS_LET20061102SCIP.PDF","1/31/2007 1:20:46 PM"
414199,"DNAME","PASS_LET","\1088741\17\L\CR_PASS_LET20061102SCIP.PDF","1/31/2007 1:20:46 PM"
414199,"EC","20061102SCIP","\1088741\17\L\CR_PASS_LET20061102SCIP.PDF","1/31/2007 1:20:46 PM"
414199,"FNAME","\1088741\17\L\CR_PASS_LET20061102SCIP","\1088741\17\L\CR_PASS_LET20061102SCIP.PDF","1/31/2007 1:20:46 PM"
414199,"PDATE","20070131","\1088741\17\L\CR_PASS_LET20061102SCIP.PDF","1/31/2007 1:20:46 PM"
414199,"PCODE","IP","\1088741\17\L\CR_PASS_LET20061102SCIP.PDF","1/31/2007 1:20:46 PM"
2317695,"RID","1088741","\1088741\17\LET\ID_CARD_1088741.PDF","9/13/2015 8:11:28 AM"
2317695,"DNAME","ID Card","\1088741\17\LET\ID_CARD_1088741.PDF","9/13/2015 8:11:28 AM"
2317695,"FNAME","\1088741\17\Let\ID_CARD_1088741.pdf","\1088741\17\LET\ID_CARD_1088741.PDF","9/13/2015 8:11:28 AM"
2317695,"PDATE","20150913","\1088741\17\LET\NA_ID_CARD_1088741.PDF","9/13/2015 8:11:28 AM"
6714777,"RID","1088741","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM
6714777,"DG","CR","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM
6714777,"DNAME","CR_UPD_LET_CIC","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM
6714777,"EC","20150828SCLH","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM
6714777,"FNAME","\1088741\17\L\Upd_LET_20150828SCLH.pdf","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM
6714777,"PDATE","20151001","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM
6714777,"PCODE","LH","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM
6714777,"UD","5704220","\1088741\17\L\UPD_LET_20150828SCLH.PDF",10/1/2015 11:02:59 AM

Open in new window

LVL 27
Eddie ShipmanAll-around developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Looks like you like to pivot your key-value list while grouping by RID and FNAME?

The problem is that your JSON does use different data than your table sample.
Eddie ShipmanAll-around developerAuthor Commented:
No, the JSON has the same data as in the sample data.
ste5anSenior DeveloperCommented:
hmm, I don't see it. The RID column does not contain 1088741 as in your JSON.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Eddie ShipmanAll-around developerAuthor Commented:
You aren't looking correctly:
162895,"RID","1088741","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"

Open in new window

Each ROW is supposed to be a column in the JSON.
The VALUE in the row containing the KEYWORD "RID" should be put into a COLUMN named "RID"
Eddie ShipmanAll-around developerAuthor Commented:
I see what you are talking about. The KEYWORD RID should be CRID so the data for the first record would look like this:
"RID","KEYWORD","VALUE","FNAME","LASTMOD"
162895,"CER","5010243","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"ST","SC","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"CRID","1088741","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"DNAME","CERT","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"EC","20051109SCIP","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"FNAME","\1088741\17\C\SC_20051109SCIP","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"
162895,"PDATE","20051130","\1088741\17\C\SC_20051109SCIP.PDF","2/1/2006 9:30:16 PM"

Open in new window

and the resulting JSON should look like this:
{"CER":"5010243", "ST":"SC", "CRID":"1088741", "DNAME":"CERT", "EC":"20051109SCIP", "FNAME":"\1088741\17\C\SC_20051109SCIP.PDF", "PDATE":"20051130", "DG":"", "PCODE":"", "UD":"", "DSENT":"20150911"}

Open in new window

Eddie ShipmanAll-around developerAuthor Commented:
The RID column in the data is ignored.
Eddie ShipmanAll-around developerAuthor Commented:
In the JSON, that is ;-)
ste5anSenior DeveloperCommented:
I would do it like this:

namespace ConsoleCS
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using Newtonsoft.Json;

    class Program
    {
        static void Main(string[] args)
        {
            DataSet sample = CreateSampleDataSet();
            PrintDataSet(sample);

            string groupByColumn = "RID";
            KeyValuePair<string, string> pivotColumn = new KeyValuePair<string, string>("KEYWORD", "VALUE");
            DataSet pivot = PivotDataSet(sample, groupByColumn, pivotColumn);
            PrintDataSet(pivot);

            var json = JsonConvert.SerializeObject(pivot);
            Console.WriteLine(json);

            Console.WriteLine("Done.");
            Console.ReadLine();
        }

        static private DataSet CreateSampleDataSet()
        {
            DataSet result = new DataSet();
            DataTable table = result.Tables.Add("Sample");
            table.Columns.Add(new DataColumn("RID", typeof(string)));
            table.Columns.Add(new DataColumn("KEYWORD", typeof(string)));
            table.Columns.Add(new DataColumn("VALUE", typeof(string)));
            DataRow row;
            row = table.NewRow();
            row["RID"] = "162895";
            row["KEYWORD"] = "CER";
            row["VALUE"] = "5010243";
            table.Rows.Add(row);
            row = table.NewRow();
            row["RID"] = "162895";
            row["KEYWORD"] = "ST";
            row["VALUE"] = "SC";
            table.Rows.Add(row);
            row = table.NewRow();
            row["RID"] = "162895";
            row["KEYWORD"] = "CRID";
            row["VALUE"] = "1088741";
            table.Rows.Add(row);
            row = table.NewRow();
            row["RID"] = "123";
            row["KEYWORD"] = "a";
            row["VALUE"] = "1";
            table.Rows.Add(row);
            row = table.NewRow();
            row["RID"] = "123";
            row["KEYWORD"] = "b";
            row["VALUE"] = "2";
            table.Rows.Add(row);
            row = table.NewRow();
            row["RID"] = "123";
            row["KEYWORD"] = "c";
            row["VALUE"] = "d";
            table.Rows.Add(row);
            return result;
        }

        static private void PrintDataSet(DataSet dataSet)
        {
            DataTable table = dataSet.Tables[0];
            Console.WriteLine(string.Format("Table: {0}", table.TableName));
            for (int i = 0; i < table.Columns.Count; i++)
            {
                Console.Write(table.Columns[i] + "\t");
            }

            Console.WriteLine();
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    Console.Write(row[i] + "\t");
                }

                Console.WriteLine();
            }
        }

        static private DataSet PivotDataSet(DataSet dataSet, string groupByColumn, KeyValuePair<string, string> pivotColumn)
        {
            DataTable source = dataSet.Tables[0];
            DataSet result = new DataSet();
            DataTable destination = result.Tables.Add("Pivot");
            destination.Columns.Add(groupByColumn, typeof(string));
            List<string> pivotKeys = source.AsEnumerable()
                .Select(r => r.Field<string>(pivotColumn.Key))
                .Distinct()
                .ToList();
            foreach (string pivotKey in pivotKeys)
            {
                destination.Columns.Add(pivotKey, typeof(string));
            }

            foreach (DataRow sourceRow in source.Rows)
            {
                DataRow destinationRow = destination.AsEnumerable()
                    .Where(k => k[groupByColumn] == sourceRow[groupByColumn])
                    .FirstOrDefault();
                if (destinationRow == null)
                {
                    destinationRow = destination.NewRow();
                    destination.Rows.Add(destinationRow);
                    destinationRow[groupByColumn] = sourceRow[groupByColumn];
                }

                destinationRow[sourceRow[pivotColumn.Key].ToString()] = sourceRow[pivotColumn.Value];
            }
            
            return result;
        }
    }
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eddie ShipmanAll-around developerAuthor Commented:
It seems to work perfectly for what we want, thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.