How to concate multiple rows in one row?

Question

Hi members, The subject is very familier and I have been searched on net so far but no solution works for me. I want a "Select" (or "Insert") query for concate rows. I want to fetch some data from data table and show in a DataGridView. Kindly check the screen shot attached, it describes much better way. 1. either I fetch records into a DataGridView by select query or... 2. first insert record in a temporaty table then fetch into the DataGridView. Please Help... Mayank Jani

Gowtham Ramar · Answer

Do you know about STUFF in sql please refer the sfuff in net i think below code was useful for you. e #tableA( uniqsr int identity(1,1), DealerID int , OrderDesc int ) insert #tableA select 1,10 union select 1,20 union select 1,30 union select 2,40 union select 2,50 select * from #tableA select distinct t1.DealerID, STUFF( ( SELECT ' ' + convert ( varchar (10), t2.OrderDesc, 120) FROM #tableA t2 where t1.DealerID = t2.DealerID FOR XML PATH ( '' )) , 1, 1, '' ) AS date from #tableA t1;

Gowtham Ramar · Answer

Hi Mayank, You want record to view pagination concept. I can't get your attached sample code. any way refer the your below url https://stackoverflow.com/questions/1900635/how-do-i-implement-pagination-in-sql-for-ms-access above url describle Ms-access sql between concept. for next record bewteen 10 to 20 , 20 to 30 pass dynamic parameter. vice versa prevics record. Thanks Gowtham.

Mayank Jani · Answer

ok, surely I'll try. Thanks... Hey can you please answer my query about to navigate next - previous records on form? I have attached sample code. when I click previous, it shows only one step previous record. I have done it with dataset. please let me know if you want me to re-post or something else... Thanks again Mayank

Amit Gupta · Answer

I'll suggest you to do this with 2 gridview. On the first gridview show all dealer name along with count of purchases, when user click on the first gridvuew, show the relevant purchases based on particular dealer.

Mayank Jani · Answer

Hey Amit, the problem is the same as your link shows. I want to get some results in a DataGridView that a user has purchased items A, B, C and D on some day from some dealer. on another day items X, Y, Z purchased on some day from the same dealer. after that if the user wants to see a report that on perticular day (or in a month) how many items purchased from the dealer, the report should be date-wise. so, on 05/12/2018 -- dealer AAA --- purchase of A, B, C, D. Presently, when I run the query it shows date, dealer name multiple times for all items. the same as mentioned in https://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query Thanks Mayank

Mayank Jani · Answer

Hi Gowtham, I have been checked those links but that does not work. it throws some error. Thanks Mayank

Gowtham Ramar · Answer

Hi Mayank Jani, i am not knowledge about MS ACCESS but below link will helpful for you https://www.codeproject.com/Questions/1104119/Create-comma-separated-list-from-table-MSACCESS http://allenbrowne.com/func-concat.html Otherwise you can get record after that concatenate row using C#

Amit Gupta · Answer

Hey Its better to create a function and get the output. Have a look to the below links https://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query However, can I know what was the program scenario, why do you want that type of format, so that we can suggest you in more optimized way

Mayank Jani · Answer

Hi Gowtham, thanks for your answer, I have checked your suggestion on net. Kindly consider that I am using MS Access as database and working on a WinApp in C# the "order desc" may vary from one another, I mean to say, It can be 4,5 or more than 7 or 8. so, the record depends on the how much quantity order placed to the dealer. Will this make any difference in the query? Thanks again.