November 28, 2011 at 8:46 pm
Hi fellow SQL gurus,
I have a organization hierarchy table below.
ID Parent Organization Parent_ID
-------------------------------------------
2036 Sunway GROUP NULL
2037 Sunway Group 10000000 2036
2038 Sunway Group SUNH GROUP 2036
2039 SUNH GROUP 2000000 2038
2040 SUNH GROUP 2050000 2038
From the existing table, I would like to concatenate the parent and organization for base members only. Please refer to table below.
ID Parent Organization Parent_ID Organization2
---------------------------------------------------------
2036 Sunway GROUP NULL SUNWAY GROUP
2037 Sunway Group 10000000 2036 10000000
2038 Sunway Group SUNH GROUP 2036 SUNH GROUP
2039 SUNH GROUP 2000000 2038 SUNH GROUP-2000000
2040 SUNH GROUP 2050000 2038 SUNH GROUP-2050000
Can anyone advise how can I transform that? Thanks.
November 28, 2011 at 10:58 pm
What would be the output for this
ID Parent Organization Parent_ID
-------------------------------------------
2036 Sunway GROUP NULL
2037 Sunway Group 10000000 2036
2038 Sunway Group SUNH GROUP 2036
2039 SUNH GROUP 2000000 2038
2040 SUNH GROUP 2050000 2038
2041 2000000 5000000 2039
Do you want to see the concatenation only for the leaf level childs?
November 28, 2011 at 11:06 pm
Hi Usman Butt,
The output for the table is exactly the second table shown in my first post. Yes, I want to see the concatenation only for the leaf level childs.
Thanks.
November 28, 2011 at 11:27 pm
Sorry, but I am still not able to understand it. Should 2039 have to be concatenated, since it has a child 2041 now? Or you just want to see the concatenation below heirarchy 2. Just to make sure Please post the output for this
ID Parent Organization Parent_ID
-------------------------------------------
2036 Sunway GROUP NULL
2037 Sunway Group 10000000 2036
2038 Sunway Group SUNH GROUP 2036
2039 SUNH GROUP 2000000 2038
2040 SUNH GROUP 2050000 2038
2041 2000000 5000000 2039
2042 2000000 6000000 2039
2043 2050000 7000000 2040
2043 5000000 8000000 2041
November 29, 2011 at 12:32 am
This would do according to your desired ouput
SELECT * INTO #temp
FROM (
SELECT 2036 AS ID,cast(NULL AS varchar(100)) AS parent,'Sunway GROUP' organization,cast(NULL AS int)parent_id
UNION ALL SELECT 2037,'Sunway Group','10000000',2036
UNION ALL SELECT 2038,'Sunway Group','SUNH GROUP',2036
UNION ALL SELECT 2039,'SUNH GROUP','2000000',2038
UNION ALL SELECT 2040,'SUNH GROUP','2050000',2038
UNION ALL SELECT 2041,'2000000','5000000',2039
) A
;WITH CTE AS (
SELECT [ID]
, [parent]
, [organization]
, [parent_id]
, CAST(ISNULL([parent] + '-','')
+ [organization] AS VARCHAR(500)) Organization2
FROM [#temp] AS T
WHERE [parent_id] IS NULL
UNION ALL
SELECT [T].[ID]
, [T].[parent]
, [T].[organization]
, [T].[parent_id]
, CAST(ISNULL(CTE.[parent] + '-','')
+ T.[organization] AS VARCHAR(500)) Organization2
FROM [#temp] AS T
INNER JOIN [CTE] ON [T].[parent_id] = [CTE].[ID]
)
SELECT * FROM [CTE]
DROP TABLE [#temp]
If this does not help your cause, then please let us know
November 29, 2011 at 1:29 am
Usman Butt (11/28/2011)
Sorry, but I am still not able to understand it. Should 2039 have to be concatenated, since it has a child 2041 now? Or you just want to see the concatenation below heirarchy 2. Just to make sure Please post the output for this
ID Parent Organization Parent_ID
-------------------------------------------
2036 Sunway GROUP NULL
2037 Sunway Group 10000000 2036
2038 Sunway Group SUNH GROUP 2036
2039 SUNH GROUP 2000000 2038
2040 SUNH GROUP 2050000 2038
2041 2000000 5000000 2039
2042 2000000 6000000 2039
2043 2050000 7000000 2040
2043 5000000 8000000 2041
Hi Usman Butt,
The output for this is...
ID Parent Organization Parent_ID Organization2
-----------------------------------------------------------
2036 Sunway GROUP NULL Sunway GROUP
2037 Sunway Group 1000000 2036 Sunway GROUP-100000
2038 Sunway Group SUNH GROUP 2036 SUNH GROUP
2039 SUNH GROUP 2000000 2038 2000000
2040 SUNH GROUP 2050000 2038 2050000
2041 2000000 5000000 2039 5000000
2042 2000000 6000000 2039 2000000-6000000
2043 2050000 7000000 2040 2050000-7000000
2043 5000000 8000000 2041 5000000-8000000
November 30, 2011 at 12:11 am
Hi Usman Butt,
I think I had figured out a way for this problem.
select *, organization as subgroup_org from org_hrchy_details
where organization in (select distinct parent from org_hrchy_details)
union
select *, parent + '-' + organization as subgroup_org from org_hrchy_details
where organization not in (select distinct parent from org_hrchy_details)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply