Collection inside collection in sql server view

Posted on 2014-10-22
Last Modified: 2016-02-18
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
Question by:Deraldo Silva
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40396532
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


Author Comment

by:Deraldo Silva
ID: 40396538
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; }
[ {"nome1", [{"1"}, {"2"},{"3"},{"4"}]}, {... }]

LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40396585
Deraldo, I'm afraid I can't help you with C#. I work only with SQL Server. Sorry.
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

by:Deraldo Silva
ID: 40396600
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
                         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.
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40396609
Deraldo, can you post an example of the output you want?
Maybe it's better for me to understand with an example.

Author Comment

by:Deraldo Silva
ID: 40396696
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.
LVL 49

Accepted Solution

Vitor Montalvão earned 500 total points
ID: 40396702
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
Nome2 f1 f3 f5 f7 f8 f9 1
Nome3 f0 f2 f4 f6 f8 f9 1

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?).

Author Closing Comment

by:Deraldo Silva
ID: 40396727
yeah. that's what I realize.

anyway, thx for your attention.
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40396788
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.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
pressing download button in c# 7 36
Making exceptions for ValidationAttribute 2 27
parse string in c# 5 31, radiobuttonlist, c# 3 38
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question