How to get a nested list in sql server

Question

I'm trying to get a list of results with a nested list. I need the fileds[3] to have the fields of all the Symbol class.

My C# model is:

public class Watchlist
{
    public Watchlist(object[] fields)
    {
        WatchlistId = (int)fields[0];
        AccountId = (string)fields[1];
        Name = (string)fields[2];
        SymbolList = SetSymbolData(fields[3]);
    }

    public int WatchlistId { get; set; }
    public string AccountId { get; set; }
    public string Name { get; set; }
    public List<Symbol> SymbolList { get; set; }
}

public class Symbol
{
    public Symbol() { }

    public Symbol(object[] fields)
    {
        Ticker = fields[0] == null ? "" : fields[0].ToString();
        Exchange = fields[1] == null ? "" : fields[1].ToString();
        Name = fields[2] == null ? "" : fields[2].ToString();
    }

    public string Ticker { get; set; }
    public string Name { get; set; }
    public string Exchange { get; set; }
}

The current SQL I have is this:

SELECT w.Id, w.Account_id, w.Name, s.symbol, s.name, s.exchange FROM
(SELECT Id, Account_id, Name FROM Watchlists where Account_id = @AccountId) w
JOIN WatchlistSymbols ws ON w.Id = ws.WatchlistId
JOIN Symbols s ON s.id = ws.SymbolId

RESULT:

Id   Account_id    Name  symbol   name            exchange
----------- -----------------------------------------------------
26     123         TEST   AAA     Listed Funds     PACF
26     123         TEST  ACQRU    Independence     NQSC
26     123         TEST   ACTD    ArcLight         NQSC

But this returns a single row for each watchlist symbol combination.

you need to provide sample data and desired output – eshirvana Aug 30 '21 at 16:20 — eshirvana, Aug 30 '21 at 16:20

score 0 · Answer 1 · answered Aug 30 '21 at 16:29

A result set from a SQL database is always a table with a fixed number of columns on every row, you can't get "jagged" or "nested" results directly from SQL as a result set.

You have three options.

One is to change SQL Server's output to JSON and parse that json using something like the Newtonsoft nuget package.

Another is to read the result set and write your own nested loops in C# to process the rows.

A third way is to use an object relational mapper, and tell it how to parse the tabular result set. Here's an example solution using Dapper

How to get a nested list in sql server

1 Answers1