Create a two dimensional array in a stored procedure in SQL Server in CSV format

Question

I know that array formats do not exist in stored procedure let alone 2-D array. But I use CSV string to store the values of the array using comma delimiter. My main question is can I store in the same way the values which I need in a 2-D format with the help of another delimiter. If yes, Can anyone guide me in the right way to do that?

The table I have is something like this....

    UserID    |   ItemID
  ---------------------------
    User1         1 
    User1         2 
    User2         3 
    User2         1

What I need is create a two dimensional result where for each item I store the users who requested it. So I want to create a csv string like this....

   "1:User1,User2,2:User1,3:User2"

Something similar to the format is it possible, to create this in stored procedure and split the string into 2-d array in c#.

Don't go there. Use a table to implement array like structure, or 2 tables with a relationship of 1 to many to implement a jagged array like structure. Keeping a delimited string in a database table should be avoided in almost any situation (in my 16 years of programming, only once I've chosen to store values as a delimited string in the database, and that's only because I've used them as delimited strings outside of the database). — Zohar Peled
– Zohar Peled, Commented Apr 26, 2015 at 13:24
I am not trying to store the string in the database. I just need to create a string like this in the stored procedure and pass it to my c# code where I can interpret as 2-d array.How to do that?? — Amalpriya
– Amalpriya, Commented Apr 26, 2015 at 13:28
Don't go there either. use a select statement to fill a DataTable and convert it to a two dimensional array in you c# code. Doing this kind of thing in c# is much easier and will work mach faster then doing it in t-sql. — Zohar Peled
– Zohar Peled, Commented Apr 26, 2015 at 13:35

Community · Accepted Answer · 2017-05-23 12:28:17Z

1

The 2D Array you describe would be nice if you need to access both ways, but bear in mind it will probably be very sparse and waste a lot of memory.

If you want to use the data you describe in C#, I would look at Linq GroupBy first. You can easily project the data into a Dictionary<string, List<int>> that holds all the items id's per Userid.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Apr 26, 2015 at 13:37

gjvdkamp

10.6k4 gold badges43 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amalpriya Over a year ago

Actually thank you for pointing me in the right direction I think this will suit my needs but can you show me a sample of how to do that or an article on how to do it step by step since I am new to Linq concept and even after spending hours to try to figure it out, I still can't understand how to implement a Linq query and how to store the results I need in IEnumerable. And since I am using ADO.NET Entity Model do i need to create to classes like mentioned in the post you linked. Truly I am clueless on whats happening please help me out.

gjvdkamp Over a year ago

Hi Amalpriya, I'd love to help you but at the moment I'm quite busy so not going be able to any time soon sorry. Good luck, GJ

Collectives™ on Stack Overflow

Create a two dimensional array in a stored procedure in SQL Server in CSV format

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related