none
help on a query RRS feed

  • Question

  • Hi,

    for a survey application i have collected these row data, just to simplify

    QUESTION_ID

    RATING

    1 good
    2 average

    3

    bad
    4 good
    1 bad
    2 average
    3

    average

    4 average

    and What i want to get is something like

    QuestionID bad average good
    1 1 - 1
    2 - 2 -
    3 1 1 -
    4 - 1 1


    so for every question i want to get sum of bad/average/good votes .
    since i don't know where to start any help or orientation o how to do that is more than welcome .

    thanks and good day


    • Edited by issam1975 Sunday, March 15, 2015 1:05 PM formating
    Sunday, March 15, 2015 1:01 PM

Answers

  • You need to create a pivot table.  Here is one method

    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("QUESTION_ID", typeof(int));
                dt.Columns.Add("RATING", typeof(string));
    
                List<List<object>> input = new List<List<object>>{
                   new List<object> {1, "good"},
                   new List<object> {2, "average"},
                   new List<object> {3, "bad"},
                   new List<object> {4, "good"},
                   new List<object> {1, "bad"}, 
                   new List<object> {2, "average"}, 
                   new List<object> {3, "average"},
                   new List<object> {4, "average"}
                }.ToList();
    
                foreach (List<object> row in input)
                {
                    dt.Rows.Add(row.ToArray());
                }
    
    
                DataTable pivot = new DataTable();
                pivot.Columns.Add("QUESTION_ID", typeof(int));
                pivot.Columns.Add("bad", typeof(int));
                pivot.Columns.Add("good", typeof(int));
                pivot.Columns.Add("average", typeof(int));
    
                var results = dt.AsEnumerable()
                    .GroupBy(x => x.Field<int>("QUESTION_ID"))
                    .Select(x => pivot.Rows.Add(new List<object> { 
                        x.Key, 
                        x.Where(y => y.Field<string>("Rating") == "bad").Count(),
                        x.Where(y => y.Field<string>("Rating") == "good").Count(),
                        x.Where(y => y.Field<string>("Rating") == "average").Count()
                    }.ToArray()));
    
    
    
            }
        }
    }
    


    jdweng

    • Marked as answer by issam1975 Monday, March 16, 2015 1:20 PM
    Sunday, March 15, 2015 2:17 PM
  • As it has been mentioned, you can use a pivot table.  You can do it with the Pivot extension on a collection too.

    http://linqlib.codeplex.com/wikipage?title=Pivot&referringTitle=Home

    <copied>

    The pivot feature enables transforming a collection of objects into a new collection of objects flattening the original hierarchy.

     <end>

    Then you can use LINQ Aggregate Methods against the collection.

    http://visualcsharptutorials.com/linq/linq-aggregate-methods

    • Marked as answer by issam1975 Monday, March 16, 2015 1:20 PM
    Sunday, March 15, 2015 7:15 PM

All replies

  • You need to create a pivot table.  Here is one method

    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("QUESTION_ID", typeof(int));
                dt.Columns.Add("RATING", typeof(string));
    
                List<List<object>> input = new List<List<object>>{
                   new List<object> {1, "good"},
                   new List<object> {2, "average"},
                   new List<object> {3, "bad"},
                   new List<object> {4, "good"},
                   new List<object> {1, "bad"}, 
                   new List<object> {2, "average"}, 
                   new List<object> {3, "average"},
                   new List<object> {4, "average"}
                }.ToList();
    
                foreach (List<object> row in input)
                {
                    dt.Rows.Add(row.ToArray());
                }
    
    
                DataTable pivot = new DataTable();
                pivot.Columns.Add("QUESTION_ID", typeof(int));
                pivot.Columns.Add("bad", typeof(int));
                pivot.Columns.Add("good", typeof(int));
                pivot.Columns.Add("average", typeof(int));
    
                var results = dt.AsEnumerable()
                    .GroupBy(x => x.Field<int>("QUESTION_ID"))
                    .Select(x => pivot.Rows.Add(new List<object> { 
                        x.Key, 
                        x.Where(y => y.Field<string>("Rating") == "bad").Count(),
                        x.Where(y => y.Field<string>("Rating") == "good").Count(),
                        x.Where(y => y.Field<string>("Rating") == "average").Count()
                    }.ToArray()));
    
    
    
            }
        }
    }
    


    jdweng

    • Marked as answer by issam1975 Monday, March 16, 2015 1:20 PM
    Sunday, March 15, 2015 2:17 PM
  • thanks man ! really appreciate this  .

    actually after your suggestion and some  searching, i have found an easier way

         var r = from o in ctx.QUESTIONS_DETAILS
                            group o by o.QST_ID_QUESTION
                                into g
                                select new
                                {
                                    k = g.Key,
                                    verygood = g.Where(x=>x.QST_RESPONSE==0).Count(),
                                    good = g.Where(x => x.QST_RESPONSE == 1).Count(),
                                    ok = g.Where(x=>x.QST_RESPONSE==2).Count(),
                                    bad = g.Where(x=>x.QST_RESPONSE==3).Count(),
                                    verybad = g.Where(x => x.QST_RESPONSE == 4).Count()
                                };
    hopefully it's the right results of the survey :p
    • Edited by issam1975 Sunday, March 15, 2015 4:56 PM
    Sunday, March 15, 2015 4:54 PM
  • As it has been mentioned, you can use a pivot table.  You can do it with the Pivot extension on a collection too.

    http://linqlib.codeplex.com/wikipage?title=Pivot&referringTitle=Home

    <copied>

    The pivot feature enables transforming a collection of objects into a new collection of objects flattening the original hierarchy.

     <end>

    Then you can use LINQ Aggregate Methods against the collection.

    http://visualcsharptutorials.com/linq/linq-aggregate-methods

    • Marked as answer by issam1975 Monday, March 16, 2015 1:20 PM
    Sunday, March 15, 2015 7:15 PM
  • The two methods are identical.  I just had to create test data.  I also put the results back into a DataTable while your results give a generic list collection. 

    jdweng

    Sunday, March 15, 2015 7:35 PM