How do I get multiple rows and insert it in an Array for each date

Question

2.33/5 (2 votes)

See more:

I need use a query that'll get multiple rows and insert it in an Array. Please refer to the example below.
Ex.

  [Day]        |   [Fruit]
01/02/2014     |  {A, B, C, D}
01/03/2014     |  {A, C, D}
01/04/2014     |  {A, B, D, E}
01/05/2014     |  {A, C}
01/06/2014     |  {A, B, C, D, E}
01/07/2014     |  {A}

Here's my code:

using (var conn = new SqlConnection("DataSource Here"))
            {
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "SELECT DISTINCT [Fruit], [DateReceive] FROM                                                      [FruitsDB].[dbo].[dummy] WHERE [Location]=@loca  AND [DateReceive] BETWEEN '2014-01-02' AND '2014-01-07' ORDER BY [DateReceive]";
                    cmd.Parameters.AddWithValue("@loca", DropDownList1.SelectedValue.ToString());
                    conn.Open();


                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var tmp = reader["Fruit"];
                            if (tmp != DBNull.Value || tmp2 != DBNull.Value)
                            {
                                ListBox1.Items.Add(tmp.ToString());
                            }
                            IList&lt;string&gt; list1 = new List&lt;string&gt;();
                            foreach (var items in ListBox1.Items)
                            {
                                list1.Add(items.ToString());
                            }
                            lbList.Text = string.Join(",", list1);
                        }
                    }
                }
            }

I hope you can help me guys.

SOLVED

-Using Christian G.'s FOR XML query

HERE'S THE SOLUTION
Thank you Mr. Christian Graus for helping me!

replacement for cmd.CommandText above

SELECT DAY(o.DateReceive) AS DayReceive,
        STUFF
        (
            (SELECT ',' + convert(varchar(99), s.Fruit)
            FROM [FruitsDB].[dbo].[dummy] s
            WHERE DAY(s.DateReceive) = DAY(o.DateReceive)
            AND Location = 'Quezon'
            GROUP BY s.Fruit
            ORDER BY s.Fruit
            FOR XML PATH('')
            ), 1, 1, ''
        ) AS Fruit
  FROM [FruitsDB].[dbo].[dummy] o
  WHERE o.DateReceive IS NOT NULL
  GROUP BY Day(o.DateReceive)

RESULT:

[Day] |   [Fruit]
  2   |  Apple, Orange, Grapes, Lemon
  3   |  Apple, Figs, Lemon
  4   |  Apple, Figs, Grapes, Strawberry
  5   |  Grapes, Lychee, Strawberry
  6   |  Apple, Orange, Grapes
  7   |  Apple, Figs, Grapes, Lychee

Posted 18-Jan-14 9:32am

Dev Jonie

Updated 18-Jan-14 21:45pm

v6

Add a Solution

Comments

Karthik_Mahalingam 18-Jan-14 15:42pm

not clear..
can u pls add more info...

Christian Graus 18-Jan-14 16:34pm

IT's VERY clear

Dev Jonie 18-Jan-14 21:23pm

Thanks its very helpful

Maciej Los 18-Jan-14 17:12pm

Please, post sample data and expected output.

BillWoodruff 18-Jan-14 19:58pm

Your source list example is transformed into a List with three inner Lists. What's the connection with "in each day, for a Week" ?

And, what "organizing principle" do you wish to use to produce (7 ?) groups ? What if day one is "figs, bananas, oranges;" and, day two is "lemons, apples, watermelon:" how will you discriminate where one day ends, and another begins, if those two days are "in sequence" ?

Missing details on how date is used to parse the data ?

Hopefully, the very talented Christian G. will help put you on the track of an SQL solution that rocks, if that's what you seek.

It's easy to write procedural code in C# to take one key-term (name of a fruit), and parse the list of strings into a List containing inner Lists which would match the example transformation you show. Do you want to see an example of that ?

Dev Jonie 18-Jan-14 21:26pm

" What if day one is "figs, bananas, oranges;" and, day two is "lemons, apples, watermelon:" how will you discriminate where one day ends, and another begins, if those two days are "in sequence" ?"

That's what I want to do.

myFruits = {{figs,bananas,oranges},{lemons,apples,watermelon}}

BillWoodruff 18-Jan-14 21:36pm

Either you need a human operator to specify a choice ... for each parsing of the lists ... for what distinguishes one day's data from the next, or you have to something in the data that delineates one day's data from another.

So, which is it ? How do you determine the boundaries of each day's data ?

Do you wish to see a procedural code example that uses the name of a specific fruit to create the inner arrays ?

Remember, that not everyone here is psychic :) ... very likely that OriginalGriff, and Christian G. are, however.

Dev Jonie 18-Jan-14 21:47pm

I updated my Question and show my latest result by the help of Christian G.'s FOR XML
I also added some examples to help you understand the question. :]

Christian Graus 18-Jan-14 22:59pm

I've updated my answer.

Dev Jonie 18-Jan-14 23:25pm

Thank you very much! :]
I got it now.

Christian Graus 18-Jan-14 23:28pm

Glad to hear it. I'm really glad that you read the article and were able to solve your issue from there.

Dev Jonie 19-Jan-14 2:31am

Mr. Christian G.,
I changed the name of the topic in this section, and created a new Question for Inserting multiple arrays in one array.

http://www.codeproject.com/Questions/712516/How-do-I-get-an-arrays-inside-an-Array-contd

BillWoodruff 18-Jan-14 22:58pm

If you cannot describe how you distinguish one day's fruits from another, there is no solution.

2 solutions

Solution 2

"[Now Proceeding in Next Phase]"

The QA format gets broken if one question is actually a ton of questions. Ask this as a new question and I'll be happy to answer it.

Looks like you're not about, and I am going out. I'd store those values in a dictionary, but the other option is a class like this:

class MyObject
(
public int id;
public List<string> Fruits;
)

and then a List<MyObject>

Obviously, a dictionary would map an id to a list of strings, also.

Posted 18-Jan-14 18:27pm

Christian Graus

Updated 18-Jan-14 18:40pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2014-01-18T10:35:00

If you know your list of fruits, you an use pivot to get numbers of each. You can also use FOR XML to create a list of fruits for each day.

Here[^] is my article on how to do this.

If you post SQL to create your table structure and put data in to it, I'd be happy to write the SQL for you.

You've done this":

SQL

SELECT distinct DAY(DateReceive) AS DayReceive,
        STUFF
        (
            (SELECT ',' + convert(varchar(99), s.Fruit)
            FROM [FruitsDB].[dbo].[dummy] s
            WHERE DAY(s.DateReceive) = DAY(s.DateReceive)
            GROUP BY s.Fruit
            ORDER BY s.Fruit
            FOR XML PATH('')
             ), 1, 1, ''
        ) AS Fruit
  FROM [FruitsDB].[dbo].[dummy]
  WHERE Location = 'Quezon'

Note your where statement. Change it to WHERE DAY(s.DateReceive) = DAY(DateReceive) or better yet, give your outer table another alias and use that.

I also think you need to lose the DISTINCT, and instead use a group by. You will need to do groupby day(datereceive), I think.