x

How to use Cursor in SQL

I have table like below, where status =0 means active and 2 bottom records merged with top one.

 ID     MasterID description     Merged Status
 ------ -------- --------------- ------ ------
 2154   NULL     Afrs- gtre      0      0
 8549   2154     Kiuhnj-poiu     1      2
 1587   2154     Hgt-uyr-njhg    1      2

Now I need all the consolidatedDescription in active Description like below:

 ID     MasterID description                              Merged Status
 ------ -------- ---------------------------------------- ------ ------
 2154   NULL     Afrs- gtre, Kiuhnj-poiu, Hgt-uyr-njhg    0      0
 8549   2154     Kiuhnj-poiu                              1      2
 1587   2154     Hgt-uyr-njhg                             1      2

how do i use Cursor ..?

more ▼

asked Jan 11 at 04:43 PM in Default

avatar image

Ranju78
0 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The short answer to the question "how do I use Cursor?" is DON'T, just don't. There is no reason whatsoever to consider using a cursor for something like this. One of the reasons to consider using a cursor is when there is a scenario where you need to, say, execute somewhat complex stored procedure for each row in your table, and that stored procedure expects some parameters gathered from the column values of the row. In most other scenarios, the knee jerk reaction to wheel out a cursor simply hints the lack of knowledge of the basic T-SQL functionality.

In this specific case, it looks like you have a scenario when the row may have NULL value in the MasterID column which probably means that there might be other rows in this table which have their respective MasterID equal to the ID value of that row where MasterID is null. This makes the data look like a hierarchy designed as adjacency list which is only 2 levels deep at most. If this assumption is incorrect then please update your question. For example, if there is a possibility that some rows with NOT NULL values in the MasterID column have some other rows which have their MasterID equal to the ID of such row then the solution will be different (to accommodate the hierarchy with whatever number of levels).

Based on the sample data in question, the following query will produce desired result:

 select
     t.ID, t.MasterID, isnull(stuff(d.List, 1, 2, ''), t.[description]) [description],
     t.Merged, t.[Status]
     from YourTable t cross apply (
         select
             ', ' + [description]
             from YourTable
             where isnull(MasterID, ID) = t.ID for xml path('')
     ) d(List);

Hope this helps.

Oleg

more ▼

answered Jan 11 at 05:36 PM

avatar image

Oleg
19.6k 3 7 28

Thank you so much .It worked.

Jan 12 at 02:23 PM Ranju78

+1. Although the advice deserves more than that.

Jan 12 at 02:25 PM Usman Butt

@Usman Butt Thank you. It looks like you have been busy, glad to see you back. Too bad the answer was not good enough to be accepted, but at least I got that "thank you mate" note from OP :)

Jan 12 at 02:59 PM Oleg

Hah. I was distracted by the version of the question in moderation, so I answered that... :-)

Jan 12 at 03:38 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2198

asked: Jan 11 at 04:43 PM

Seen: 27 times

Last Updated: Jan 12 at 05:00 PM

Copyright 2018 Redgate Software. Privacy Policy