CODEWITHSUNDEEP
sqlsql-server
jishan siddique
jishan siddique

Reputation: 1893

How to display record in hierarchy single sql table

I have one table PrimaryActivity in there table some columns present such as.

SELECT [PrimaryActivityID],[ActivityName] FROM [dbo].[PrimaryActivity] (NOLOCK)

--Current Query result 
PrimaryActivityID ActivityName
----------------- -----------------------------
3                administrator
122              administrator_icon
126              administrator_icon_delete
124              administrator_icon_insert
123              administrator_icon_search
125              administrator_icon_update
121              home
131              home_clearcache
130              home_dashboard
2                mainmenu
105              mainmenu_acsession
109              mainmenu_acsession_delete
107              mainmenu_acsession_insert
106              mainmenu_acsession_search
108              mainmenu_acsession_update
1132             mainmenu_callfortopics

Expected result

PrimaryActivityID ParentActivity    ActivityName
---------------------------------------------------------------
3                 administrator      NULL
122                                  administrator_icon
126                                  administrator_icon_delete
124                                  administrator_icon_insert
123                                  administrator_icon_search
125                                  administrator_icon_update
121              home                NULL
131                                  home_clearcache
130                                  home_dashboard
2                mainmenu            NULL
105                                  mainmenu_acsession
105              mainmenu_acsession  NULL
109                                  mainmenu_acsession_delete
107                                  mainmenu_acsession_insert
106                                  mainmenu_acsession_search
108                                  mainmenu_acsession_update
1132                                 mainmenu_callfortopics

Can some help me to get the exact output?

I'll already prepare some examples but not able to get the exact output below the query.

SELECT [PrimaryActivityID]
       ,NULL [ParentActivity]
       ,[ActivityName] 
FROM [dbo].[UM_PrimaryActivity] (NOLOCK)
UNION 
SELECT [PrimaryActivityID]
       ,[ActivityName] [ParentActivity]
      ,[ActivityName] 
FROM [dbo].[UM_PrimaryActivity] (NOLOCK)

Upvotes: 0

Views: 42

Answers (2)

SQLpro
SQLpro

Reputation: 5103

AS an example :

SELECT PrimaryActivityID, ActivityName, 
       LEFT(ActivityName, COALESCE(NULLIF(CHARINDEX('_', ActivityName), 0), LEN(ActivityName)+1)-1) AS LEADER,
       RIGHT(ActivityName, LEN(ActivityName) - CHARINDEX('_', ActivityName)) AS LAGER
FROM PrimaryActivity

Will do the job and you can use it recursively...

And with a recursive query :

WITH 
T AS
(
SELECT PrimaryActivityID, ActivityName, 1 AS STEP,
       LEFT(ActivityName, COALESCE(NULLIF(CHARINDEX('_', ActivityName), 0), LEN(ActivityName)+1)-1) AS LEADER,
       RIGHT(ActivityName, LEN(ActivityName) - NULLIF(CHARINDEX('_', ActivityName), 0)) AS LAGER
FROM   PrimaryActivity
UNION  ALL
SELECT PrimaryActivityID, ActivityName, STEP + 1,
       LEFT(LAGER, COALESCE(NULLIF(CHARINDEX('_', LAGER), 0), LEN(LAGER)+1)-1) AS LEADER,
       RIGHT(LAGER, LEN(LAGER) - NULLIF(CHARINDEX('_', LAGER), 0)) AS LAGER
FROM   T
WHERE  LAGER IS NOT NULL
)
SELECT PrimaryActivityID, ActivityName, STEP, LEADER AS part, SPACE(STEP - 1) + LEADER AS part_hierarchy
FROM   T
ORDER  BY ActivityName, STEP

Upvotes: 1

George Joseph
George Joseph

Reputation: 5922

Here is a way to get this done...

with data
  as (
select PrimaryActivityId
       ,case when charindex('_',ActivityName) <> 0 then
                  substring(ActivityName,1,charindex('_',ActivityName)-1) 
             else ActivityName 
         end as ParentActivity
       ,case when charindex('_',ActivityName) <> 0 then ActivityName end as Activityname
 from dbo.PrimaryActivity
      )
select PrimaryActivityId
      ,case when ActivityName is null then ParentActivity end as ParentActivity_mod
      ,ActivityName as ActivityName_mod
  from data

+-------------------+--------------------+---------------------------+
| PrimaryActivityId | ParentActivity_mod |     ActivityName_mod      |
+-------------------+--------------------+---------------------------+
|                 3 | administrator      |                           |
|               122 |                    | administrator_icon        |
|               126 |                    | administrator_icon_delete |
|               124 |                    | administrator_icon_insert |
|               123 |                    | administrator_icon_search |
|               125 |                    | administrator_icon_update |
|               121 | home               |                           |
|               131 |                    | home_clearcache           |
|               130 |                    | home_dashboard            |
|                 2 | mainmenu           |                           |
|               105 |                    | mainmenu_acsession        |
|               109 |                    | mainmenu_acsession_delete |
|               107 |                    | mainmenu_acsession_insert |
|               106 |                    | mainmenu_acsession_search |
|               108 |                    | mainmenu_acsession_update |
|              1132 |                    | mainmenu_callfortopics    |
+-------------------+--------------------+---------------------------+

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a5891b9bec680fd9332505bd20b7bfb3

Upvotes: 1

Related Questions