Row Concatenation
-
I have followign two tables, and want to update TB1 such that IDs col gets updated PARAMETER tables TB1 Dt desk IDs ----------- ---------- ---------- 1 PT 1 AB 2 PT 2 AB TB2 Dt desk book IDs ----------- ---------- ---------- ----------- 1 PT PT1 100 1 PT PT2 200 1 PT PT3 300 1 AB AB1 400 2 PT PT1 500 2 PT PT2 600 2 PT PT3 700 2 AB AB1 800 Final RESULT TB1 Dt desk IDs ----------- ---------- ---------- 1 PT 100, 200, 300 1 AB 400 2 PT 500, 600, 700 2 AB 800 Any pointers Thanks Ruchi
-
I have followign two tables, and want to update TB1 such that IDs col gets updated PARAMETER tables TB1 Dt desk IDs ----------- ---------- ---------- 1 PT 1 AB 2 PT 2 AB TB2 Dt desk book IDs ----------- ---------- ---------- ----------- 1 PT PT1 100 1 PT PT2 200 1 PT PT3 300 1 AB AB1 400 2 PT PT1 500 2 PT PT2 600 2 PT PT3 700 2 AB AB1 800 Final RESULT TB1 Dt desk IDs ----------- ---------- ---------- 1 PT 100, 200, 300 1 AB 400 2 PT 500, 600, 700 2 AB 800 Any pointers Thanks Ruchi
Is this on sql server? If so, your best bet might be to write a cursor to build each of your id strings ('100,200,300') and then update. I've been trying to come up with a clever way to do it in one SQL statement, doing self joins of tb2 to itself...but I am not getting anywhere... Typically, when I need to denormalize data like this, I do the denormalization in the application (typically c#...but that's details). SQL just does not lend itself to this kind of thing nearly as readily as ADO.NET / more procedural programming approaches do. Hope this helps Bill
-
Is this on sql server? If so, your best bet might be to write a cursor to build each of your id strings ('100,200,300') and then update. I've been trying to come up with a clever way to do it in one SQL statement, doing self joins of tb2 to itself...but I am not getting anywhere... Typically, when I need to denormalize data like this, I do the denormalization in the application (typically c#...but that's details). SQL just does not lend itself to this kind of thing nearly as readily as ADO.NET / more procedural programming approaches do. Hope this helps Bill
Thanks very much for your response. I myself am doing the same thing you suggested - Cursor & Updates. But it is kind of expensive operation, when temp table is huge (depending on param passed to SP) I need this result set for C# application. Could you please give me pointers on how you do it in ADO.NET Thanks Ruchi
-
Thanks very much for your response. I myself am doing the same thing you suggested - Cursor & Updates. But it is kind of expensive operation, when temp table is huge (depending on param passed to SP) I need this result set for C# application. Could you please give me pointers on how you do it in ADO.NET Thanks Ruchi
Ruchi Gupta wrote: But it is kind of expensive operation, when temp table is huge Agreed. Which is why I was trying to be clever...the
update...fromis often handy for complex updates, but I can't come up with a single query that will generate your desired result strings ('100,200,300','400', etc). Besides, that approach required that you know the max number of strings (in your example, 3) before hand. You mention passing parameters for the first time above. Are you updating only a few records in TB1? That might make this easier. I was assuming you were updating the whole table. Anyway in ADO, the idea is to get your data from TB2 in order, loop through the results building the new IDs string for each entry in TB1 and then do the update. C# doesn't suffer from the horrible performance penalties of SQL for looping. Something like: -Load TB1 into a DataTable using a dataadapter (if you set the PK, you can use a SqlCommandBuilder to automatically generate your update command) -Load TB2, sorted by DT,Desk into a second datatable -create a string variable to accumulate the id string; -Cycle through every row of this second table. -if the current row has the same DT and Desk as the previous row, just add the ID to the id string with the comma -other wise, set the IDs column of the Row in the first DataTable with the matching DT and Desk to the accumulated ID string. Use DataTable.Find() to find the row. Clear the ID string. -At the end, use the original dataadapter to update the database. This way, you can do all the itteration in C#, which does it well, rather than in SQL which is terrible at it. Hope this helps Bill