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 ------------------------------------------- Were 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] -----------------------------------------------------------------------------