Collection inside collection in sql server view

Hi.
I have two tables. relation 1:N. when I create a view, I got one record for each item in the N table. How could I get a single record with a collection of the N table inside the first record? Is it possible?
From this view, we will create a stored procedure and mapped it with EF 6 to use into a c# web app.

thx in advance
example.png
Deraldo SilvaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
A GROUP BY clause will do the trick. You just need to decide if you want the MIN or MAX account:
SELECT users.Name, MAX(accounts.Account)
FROM accounts
	INNER JOIN users ON (accounts.userid = users.userid)
GROUP BY users.Name

Open in new window

Deraldo SilvaAuthor Commented:
Hi Vitor, thx for your attention.
with this query I got the number of accounts that a user owns. I need this (in c# terms)

public class User
{
  public string Name { get; set; }
  public ICollection<string> accounts { get; set; }
}
or,
[ {"nome1", [{"1"}, {"2"},{"3"},{"4"}]}, {... }]

regards
Vitor MontalvãoMSSQL Senior EngineerCommented:
Deraldo, I'm afraid I can't help you with C#. I work only with SQL Server. Sorry.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Deraldo SilvaAuthor Commented:
Vitor, I do not have any question about c#, it's an example. because of this, I put two different examples, in  c# and in json.
ok. let me explain better.
My question is about sql server.
as I show in the picture, when i did my view, the sql is

SELECT        dbo.USERS.Name, dbo.ACCOUNTS.Account
FROM            dbo.ACCOUNTS INNER JOIN
                         dbo.USERS ON dbo.ACCOUNTS.UserID = dbo.USERS.UserID

but, with this query I got this result
Nome1 1
Nome1 2
Nome1 3
Nome1 4

If I have several fields in the Users table, these fields will be repeated, for all accounts, right? like this
Nome1 f1 f2 f3 f4 f5 f6 1
Nome1 f1 f2 f3 f4 f5 f6 2 and so on.

what I want: a single record where the accounts is another record set, inside the first one.

I don't know if it is possible.
maybe a select inside a select.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Deraldo, can you post an example of the output you want?
Maybe it's better for me to understand with an example.
Thanks.
Deraldo SilvaAuthor Commented:
thats the problem. I don't know how to show you the example as a sql result. I think that there is no such thing in sql. a recordset inside an record. a list as field element. wow! maybe converting one recordset into a xml value before output. So the user record, with all its fields and one field will be an xml with several accounts inside it.
is it possible with one view? or one stored procedure?

sorry for the inconvenience, this is like an handshake between two different expertises.
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server it's good to return data but for formatting I would prefer a program language or a reporting tool.
What I understood is that you want something like:
Nome1 f1 f2 f3 f4 f5 f6 1
                                         2
                                         3
                                         4
Nome2 f1 f3 f5 f7 f8 f9 1
                                         2
Nome3 f0 f2 f4 f6 f8 f9 1
                                         ...
Right?

If so, the only think SQL Server can do is to return it like:
Nome1 f1 f2 f3 f4 f5 f6 1
Nome1 f1 f2 f3 f4 f5 f6 2
Nome1 f1 f2 f3 f4 f5 f6 3
Nome1 f1 f2 f3 f4 f5 f6 4
Nome2 f1 f3 f5 f7 f8 f9 1
Nome2 f1 f3 f5 f7 f8 f9 2
Nome3 f0 f2 f4 f6 f8 f9 1

And then you format it as you need (suppress repeating values?).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deraldo SilvaAuthor Commented:
yeah. that's what I realize.

anyway, thx for your attention.
Olaf DoschkeSoftware DeveloperCommented:
Linq can do what you need, more precise the linq extension methods, not a linq query.
            using (var entities = new YourDatabaseEntities())
            {
                Users = new Collection<User>(
                    entities.users.Select(usr => new User
                    {
                        Name =  usr.UserName,
                        Accounts = usr.accounts.Select(acc => new Account
                        {
                            AccountNo = acc.AccountNumber
                        })
                    }));
            }

Open in new window

The User class then will need to have a string Name property and a IEnumerable<Account> Accounts property.
 
Bye, Olaf.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.