How to create nonexistent columns in a Datatable?

Question

Here is my query:

select 
u.nome as analista, 
s.descricao as situacao, 
count(*) as qtd                            
from tb_projeto p
inner join tb_usuario u on u.id_usuario = p.id_usuario
inner join tb_situacao s on s.id_situacao = p.id_situacao
group by analista, situacao
order by analista 

And the result of it:

When I receive this in a Datatable I make some rebounds and turn these 3 columns into a Cross Table, but as not all analysts have the values of the quantities "Disapproved" greater than zero it does not appear in Datatable which breaks my total in the interface:

public DataTable CrossTable(DataTable dtS, String leftColumn, String topField, String dataValue, String pFix = "F_")
    {
        if (dtS == null)
        {
            return null;
        }

        DataTable dtOut = new DataTable();
        DataTable dtRowTitle = new DataTable();
        DataTable dtColHeader = new DataTable();

        dtRowTitle = dtS.DefaultView.ToTable(true, dtS.Columns[leftColumn].ColumnName);
        dtColHeader = dtS.DefaultView.ToTable(true, dtS.Columns[topField].ColumnName);

        DataColumn dColx = new DataColumn();            

        dColx.ColumnName = leftColumn;
        dColx.Caption = leftColumn;
        dtOut.Columns.Add(dColx);            

        foreach (DataRow drow in dtColHeader.Rows)
        {
            DataColumn dCol = new DataColumn();
            dCol.ColumnName = pFix + drow[topField].ToString().Trim();
            dtOut.Columns.Add(dCol);
        }

        DataColumn dColx2 = new DataColumn();
        dColx2.ColumnName = "TOTAL";
        dtOut.Columns.Add(dColx2);

        DataRow drowx;

        foreach (DataRow drow in dtRowTitle.Rows)
        {
            drowx = dtOut.NewRow();
            drowx[0] = drow[leftColumn];
            dtOut.Rows.Add(drowx);
        }

        Int32 xVal = 0, yVal = 0;
        string analista = null;
        int total = 0;

        foreach (DataRow mRow in dtS.Rows)
        {
            string xRowVal = mRow[leftColumn].ToString(), dataVal = mRow[dataValue].ToString(), yColVal = mRow[topField].ToString().Trim();

            foreach (DataRow nRow in dtOut.Rows)
            {
                if (xRowVal == nRow[0].ToString())
                {
                    for (xVal = 0; xVal < nRow.Table.Columns.Count; xVal++)
                    {
                        if (nRow.Table.Columns[xVal].ColumnName == pFix + yColVal)
                        {                                
                            if (xVal == 1)
                            {
                                total = 0;
                                analista = xRowVal;
                            }

                            Int32 rIndex = dtOut.Rows.IndexOf(nRow);
                            dtOut.Rows[rIndex][xVal] = dataVal;

                            if (analista == xRowVal)
                            {                                    
                                total += Convert.ToInt32(dataVal);
                            }

                            if (xVal + 2 == nRow.Table.Columns.Count)
                            {
                                dtOut.Rows[rIndex][3] = total.ToString();
                            }
                        }
                    }

                }
            }
        }

        dtOut.DefaultView.Sort = dtOut.Columns[0].ColumnName;
        return dtOut;
    }

With some analysts' "Fail" columns not even being processed my total gets wrong. How to solve this problem?

Answer 1

If your columns do select and the records of table tb_situation are always fixed, you can do right in your select, that way:

-- considerando que o id_situacao
-- 1 = 'APROVADO'
-- 2 = 'REPROVADO'

select
  u.nome ANALISTA,
  sum(case when (s.id_situacao = 1) then 1 else 0 end) APROVADO,
  sum(case when (s.id_situacao = 2) then 1 else 0 end) REPROVADO,
  count(*) TOTAL
from 
  tb_projeto as p
  inner join tb_usuario u on (u.id_usuario = p.id_usuario)
  inner join tb_situacao s on (s.id_situacao = p.id_situacao)
group by 
  ANALISTA
order by 
  ANALISTA

See working on SQL Fiddle.

If you really want to do it via code and using your select, you could do something similar to this:

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

namespace Teste01
{
    class MainClass
    {
        public static void Main (string[] args)
        {
            DataTable dtEntrada = new DataTable ();
            dtEntrada.Columns.Add (new DataColumn ("ANALISTA", typeof(String)));
            dtEntrada.Columns.Add (new DataColumn ("SITUACAO", typeof(String)));
            dtEntrada.Columns.Add (new DataColumn ("QTD", typeof(Int32)));

            dtEntrada.Rows.Add (new Object[] { "EDSON", "APROVADO", 1 });
            dtEntrada.Rows.Add (new Object[] { "EDSON", "REPROVADO", 2 });
            dtEntrada.Rows.Add (new Object[] { "EDUARDO RAMOS", "APROVADO", 3 });
            dtEntrada.Rows.Add (new Object[] { "EDUARDO TORQUATO", "APROVADO", 1 });
            dtEntrada.Rows.Add (new Object[] { "LUCAS", "APROVADO", 1 });
            dtEntrada.Rows.Add (new Object[] { "LUCAS", "REPROVADO", 1 });

            DataTable dtSaida = new DataTable ();
            dtSaida.Columns.Add (new DataColumn ("ANALISTA", typeof(String)));
            dtSaida.Columns.Add (new DataColumn ("APROVADO", typeof(Int32)));
            dtSaida.Columns.Add (new DataColumn ("REPROVADO", typeof(Int32)));
            dtSaida.Columns.Add (new DataColumn ("TOTAL", typeof(Int32)));

            var lstAnalistas = (from a in dtEntrada.Select()
                                group a by a["ANALISTA"] into g
                                orderby g.Key
                                select (String) g.Key).ToList<String>();

            int aprovado, reprovado, total;

            foreach (var analista in lstAnalistas) {
                DataRow linhaAprovado = (from a in dtEntrada.Select ()
                                         where (String) a ["ANALISTA"] == analista && 
                                               (String) a ["SITUACAO"] == "APROVADO"
                                         select a).FirstOrDefault ();

                DataRow linhaReprovado = (from a in dtEntrada.Select ()
                                          where (String) a ["ANALISTA"] == analista && 
                                                (String) a ["SITUACAO"] == "REPROVADO"
                                          select a).FirstOrDefault ();

                if (linhaAprovado != null)
                    aprovado = (int) linhaAprovado ["QTD"];
                else
                    aprovado = 0;

                if (linhaReprovado != null)
                    reprovado = (int) linhaReprovado ["QTD"];
                else
                    reprovado = 0;

                total = aprovado + reprovado;
                dtSaida.Rows.Add (new Object[] { analista, aprovado, reprovado, total });
            }

            foreach (DataRow linha in dtSaida.Rows) {
                foreach (DataColumn coluna in dtSaida.Columns) {
                    Console.Write (linha [coluna.ColumnName].ToString () + "\t");
                }
                Console.WriteLine ();
            }

        }
    }
}

See working on . NET Fiddle.

Stay tuned for the issue of names of equal analysts, it may be necessary to use their id to differentiate them.

Note: the part of the code where I created dtEntrada was only to be able to generate an output with the data you entered.

Extra

Conditional Expressions

Answer 2

Matthew, I suggest you use the pivot table in your query. This function does exactly what you want, transforms rows into columns, and it is not necessary to make rebounds in your code c#.

Answer 3

coalesce returns the value of the second parameter if the first is null

coalesce(count(*), 0) as qtd

http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL