0

Table name Hierarchy Columns H_KEY, FATHER_KEY and CHILD_KEY

in below table, for the father_key

Rajachola1 has 3 childrens like Rajendra1, Rajendra2, Rajendra3

enter image description here

and i want output like

below please help

Fatherkey column we have populated the rajachola1 and in the subordinates column we need to bring all the 3 childrens under this column like below

enter image description here

Ram
  • 59
  • 1
  • 6
  • As a general principle, you don't. It's an anti pattern. You application layer may denormalise data, but doing it within a relational data is generally a very bad idea. [is-storing-a-delimited-list-in-a-database-column-really-that-bad](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – MatBailie Nov 17 '22 at 15:35

1 Answers1

0
SELECT
 [Father_Key],[H_Key], 
 STUFF(
     (SELECT DISTINCT '+' + child_key
      FROM Hierachy_Table
      WHERE [Father_Key] = a.[Father_Key] AND H_Key= a.H_Key
      FOR XML PATH (''))
      , 1, 1, '')  AS subordinates
FROM Hierachy_Table AS a
GROUP BY [Father_Key], H_Key
urvi jain
  • 49
  • 8