x

How to get consolidated description

What if the IDs' are in '947C623E-906A-E511-8C8E-0050569E5B20' format. The cast into int does not work.

i have different scenario in same table in sql server and i need to get all the description in active column. i have table like this:

 ID     MasterID description          Merged Status
 ------ -------- -------------------- ------ ------
 2154   NULL     Afrs, gtre           0      0
 8549   2154     Kiuhnj-poiu          1      2
 1587   2154     Hgt-uyr-njhg         1      2
 5842   NULL     lokj-mknh,mnjt       0      0
 7458   5842     vbgfd,hytre          1      2
 3628   7458     null                 1      2
 7493   3628     drfs-mjnb-nbhg       1      2
 1023   NULL     NULL                 0      0
 1529   1023     bhgyt-kmnj           1      2
 3291   1023     mkrew-ytre,nhbv98    1      2
 4871   3291     dse4r,ki98m,mkjhy-lk 1      2

and i need below output:

 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
 5842   NULL     lokj-mknh,mnjt,vbgfd,hytre,drfs-mjnb-nbhg          0      0
 7458   5842     vbgfd,hytre                                        1      2
 3628   7458     null                                               1      2
 7493   3628     drfs-mjnb-nbhg                                     1      2
 1023   NULL     bhgyt-kmnj,mkrew-ytre,nhbv98,dse4r,ki98m,mkjhy-lk  0      0
 1529   1023     bhgyt-kmnj                                         1      2
 3291   1023     mkrew-ytre,nhbv98                                  1      2
 4871   3291     dse4r,ki98m,mkjhy-lk                               1      2
more ▼

asked Jan 12 at 03:08 PM in Default

avatar image

Ranju78
0 1

@Ranju78 What controls the order of the rows for the same master? For example, in your sample data, rows # 2 and # 3 both belong to the same parent (row # 1, ID = 2154). However, the ID in row # 2 (8549) is higher than ID in row # 3 (1587). Do you know why? There has to be something what dictates such order. Without knowing that it is, there is no guaranteed way to consolidate the descriptions in the order like in your desired result. For example, an attempt to sort children for the same master by their level and then by their ID will cause the rows to be repositioned and descriptions combined in the different from expected order. This information is very important. Please elaborate on it. Thank you.

Jan 12 at 05:12 PM Oleg

@Ranju78 Just replace all occurrences of AS INT to AS UNIQUEUDENTIFIER and the script in my answer should work as expected then. Thank you.

5 days ago Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

As @ThomasRushton ♦♦ already pointed out, this question appears to be closely related to the question which was asked yesterday except the sample data in this question hints the possibility that the data in the table represents a multi-level hierarchy designed as adjacency list. The task appears to be to gather all descriptions from all records which ultimately (directly or indirectly) belong to the same master and combine all such descriptions into the single list for each such ultimate master. For example, we have records with ID equal to 1529 and 3291 which directly map to their parent with ID 1023, and one of these 2 records (with ID equal to 3291) also has a child record with ID 4871, so the descriptions of all 3 child/grandchild records need to be consolidated (added) to the existing description of the ultimate parent itself, etc.

Because the hierarchy is multi-leveled, matching MasterID with ID is not enough. One way to handle this scenario is via recursive CTE, commonly used for hierarchical queries in T-SQL. Recursive CTE in T-SQL is the alternative to connect by clause in Oracle PL-SQL, it serves the same purpose as it allows the hierarchies to go many levels deep (100 is a default limit in T-SQL which can be increased if needed but such increase is seldom needed in real life scenarios).

Here is the script which will produce desired results:

 ;with hierarchy as (
     select
         cast(ID as int) UltimateMaster, ID, 0 TheLevel, cast(ID as varchar(max)) Sort, [description]
         from YourTable
         where MasterID is null
     union all
     select
         case 
             when h.TheLevel = 0 then t.MasterID 
             else cast(substring(h.Sort, 1, charindex('/', h.Sort) - 1) as int)
         end, t.ID,
         h.TheLevel + 1, h.Sort + '/' + cast(t.ID as varchar(10)), t.[description]
         from YourTable t inner join hierarchy h
             on t.MasterID = h.ID
 )
     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 hierarchy
                  where UltimateMaster = t.ID for xml path('')
          ) d(List);

Based on the sample data in question, the query above produces the following result:

 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
 5842   NULL     lokj-mknh,mnjt, vbgfd,hytre, drfs-mjnb-nbhg        0      0
 7458   5842     vbgfd,hytre                                        1      2
 3628   7458     NULL                                               1      2
 7493   3628     drfs-mjnb-nbhg                                     1      2
 1023   NULL     bhgyt-kmnj, mkrew-ytre,nhbv98, dse4r,ki98m,mkjhy-l 0      0
 1529   1023     bhgyt-kmnj                                         1      2
 3291   1023     mkrew-ytre,nhbv98                                  1      2
 4871   3291     dse4r,ki98m,mkjhy-lk                               1      2

Hope this helps.

Oleg

more ▼

answered Jan 12 at 07:27 PM

avatar image

Oleg
19.5k 3 7 28

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

Something like this?

 declare @t table (
 ID int NOT NULL, MasterID int NULL, description varchar(250) NULL, Merged int not null, Status int not null);
 
 insert into @t values
 ( 2154,  Null, 'Afrs, gtre', 0, 0),
 ( 8549,  2154, 'Kiuhnj-poiu', 1, 2),
 ( 1587,  2154, 'Hgt-uyr-njhg', 1, 2),
 ( 5842,  null, 'lokj-mknh,mnjt', 0, 0),
 ( 7458,  5842, 'vbgfd,hytre', 1, 2),
 ( 3628,  7458, NULL, 1, 2),
 ( 7493,  3628, 'drfs-mjnb-nbhg',1, 2),
 ( 1023,  null, NULL, 0, 0),
 ( 1529,  1023, 'bhgyt-kmnj',1, 2),
 ( 3291,  1023, 'mkrew-ytre,nhbv98',1, 2 ),
 ( 4871,  3291, 'dse4r,ki98m,mkjhy-lk', 1, 2);
 
 select id, masterid, 
 case when masterid is null then 
     (select stuff((select isnull(',' + description,'') FROM @t t2 
                    where (t2.id = t1.id AND t2.masterid IS NULL) OR (t2.masterid = t1.id) 
                    for xml path(''),type).value('.','varchar(max)'),1,1,''))
 else description end as description, merged, status
 from @t t1

(corrected to handle NULLs)

more ▼

answered Jan 12 at 03:29 PM

avatar image

ThomasRushton ♦♦
42.1k 20 57 53

@ThomasRushton ♦♦ This might not handle the "grandchildren" (and further levels if any). The similar question was asked and answered yesterday. I raised a concern about the possibility of the more than 2 levels in the hierarchy (data looked like adjacency list but there was not enough data in the sample to confirm), but unfortunately did not receive any confirmation from OP. Now it does look like it is an adjacency list indeed.

Jan 12 at 04:26 PM Oleg

Good point, @Oleg. Not that we can do anything about the OP responding (or otherwise)...

Fingers crossed that the adjacency list is really all that's needed.

Jan 12 at 04:59 PM ThomasRushton ♦♦

Thank you so much for the answer. But the query did not work for this scenario:

5842, null, 'lokj-mknh,mnjt', 0, 0 7458, 5842, 'vbgfd,hytre', 1, 2 3628, 7458, NULL, 1, 2 7493, 3628, 'drfs-mjnb-nbhg',1, 2

getting this output: the active has only two description consolidated

5842, null, 'lokj-mknh,mnjt,vbgfd,hytre', 0, 0 7458, 5842, 'vbgfd,hytre', 1, 2 3628, 7458, NULL, 1, 2 7493, 3628, 'drfs-mjnb-nbhg',1, 2

Jan 12 at 07:12 PM Ranju78

@Ranju78 Yes, Thomas and I already discussed it. Please check my answer and let me know if it works. The solution is based on some assumptions due to lack of information in question. I hope that the script produces desired results.

Jan 12 at 07:31 PM Oleg

Thank you so much

Jan 12 at 09:49 PM Ranju78
(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:

x2197

asked: Jan 12 at 03:08 PM

Seen: 44 times

Last Updated: 5 days ago

Copyright 2018 Redgate Software. Privacy Policy