RE: [sqlite] Getting an Array or list in a select statement...

[Samuel R. Neff]

We use a custom aggregate function called DisplayList to do exactly what
you're talking about.  C# code follows.


SELECT
  U.UserName,
  DisplayList(R.RoleName)
FROM 
  Users U
      INNER JOIN 
  Xref_Users_Roles X
      ON 
  U.UserID = X.UserID
      INNER JOIN
  Roles R
      ON
    X.RoleID = R.RoleID
GROUP BY
  U.UserName
ORDER BY
  U.UserName, 
  R.RoleName        

HTH,

Sam 


-------------------------------------------
We’re Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 


using System;
using System.Collections.Generic;
using System.Data.SQLite;
using Cube.MasterService.Common;

namespace Cube.MasterService.Storage
{
  /// <summary>
  /// Creates a list of data from the aggregate source.
  /// </summary>
  [SQLiteFunction(Name = "DisplayList", Arguments = -1, FuncType =
FunctionType.Aggregate)]
  public class SQLiteDisplayList : SQLiteFunction
  {
    public override void Step(object[] args, int stepNumber, ref object
contextData)
    {
      if (args.Length == 0)
      {
        if (contextData == null)
        {
          contextData = new ArgumentException("DisplayList requires at least
one argument.");
        }
        return;
      }

      DisplayListContext displayListContext;

      if (contextData == null)
      {
        contextData = displayListContext = new
DisplayListContext(args.Length > 1 ? DbConvert.ToString(args[1]) : ",",
 
args.Length > 2 ? DbConvert.ToBoolean(args[2]) : false);
      }
      else
      {
        displayListContext = (DisplayListContext) contextData;
      }

      displayListContext.Add(DbConvert.ToString(args[0]));
    }

    public override object Final(object contextData)
    {
      if (contextData == null)
      {
        return null;
      }

      if (contextData is Exception)
      {
        return contextData;
      }

      return contextData.ToString();
    }

    private class DisplayListContext
    {
      private readonly string _separator;
      private readonly bool _sort;
      private readonly List<string> _data = new List<string>();

      public DisplayListContext(string separator, bool sort)
      {
        _separator = separator;
        _sort = sort;
      }

      public void Add(string value)
      {
        _data.Add(value);
      }

      public override string ToString()
      {
        if (_sort)
        {
          _data.Sort();
        }
        return String.Join(_separator, _data.ToArray());
      }
    }
  }
}



-----Original Message-----
From: jose isaias cabrera [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 11, 2007 1:48 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Getting an Array or list in a select statement...


Greetings.

So, I know that I can do a single select and get a list of the different 
items found on a column, but what I would like to do is to get this same 
list on another select statement.  Let me explain;

I have this select statement,

"select ProjID,
       cust,
       proj,
       PClass,
       PSubClass,
       min(bdate),
       max(ddate),
       max(edate),
       lang,
       vendor,
       sum(invoice),
       sum(ProjFund),
       sum(PMTime),
       A_No from LSOpenJobs
 where ProjID = 24
 AND
  (
    (bdate BETWEEN '2007-09-01' AND '2007-09-01') AND
    (ddate BETWEEN '2007-09-01' AND '2007-09-01') AND
    (edate BETWEEN '2007-09-01' AND '2007-09-01')
  )
 AND
  PClass!='Quote' group by ProjID;";

And this is working fine.  However, I would like to get a list of all the 
different PSubClass items found on this select statement.  Can this be done 
on this select or do I have to do the single select to get this item also?

thanks,

josé 


----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------