question

aRookieBIdev avatar image
aRookieBIdev asked

SQL Query Help needed!

Hi , I am quite stuck at this point in arriving at my expected result. Kindly provide your suggestions. My expected result.I need to Group the data together and give that an id ![alt text][1] CREATE TABLE [PersonAttributeStage_temp]( [PersonAttributeStageID] [bigint] IDENTITY(1,1) NOT NULL, [PersonID] [bigint] NULL, [Value] [varchar](45) NOT NULL, [CreatedDate] [smalldatetime] NOT NULL, [CreatedUser] [varchar](45) NOT NULL ) INSERT INTO [PersonAttributeStage_temp] ([PersonID] ,[Value] ,[CreatedDate] ,[CreatedUser]) VALUES (1 ,'Language-SW' ,GETDATE() ,'TestUser') GO INSERT INTO [PersonAttributeStage_temp] ([PersonID] ,[Value] ,[CreatedDate] ,[CreatedUser]) VALUES (1 ,'Language-EN' ,GETDATE() ,'TestUser') GO CREATE TABLE [PersonAttributeProcessStage_temp]( [PersonAttributeProcessStageID] [bigint] IDENTITY(1,1) NOT NULL, [PersonAttributeStageID] [bigint] NOT NULL, [AttributeValue] [varchar](30) NULL, [ParentPersonAttributeProcessStageID] [bigint] NULL ) INSERT INTO [PersonAttributeProcessStage_temp] ([PersonAttributeStageID] ,[AttributeValue] ,[ParentPersonAttributeProcessStageID]) VALUES (1 ,'Parent101' ,NULL) GO INSERT INTO [PersonAttributeProcessStage_temp] ([PersonAttributeStageID] ,[AttributeValue] ,[ParentPersonAttributeProcessStageID]) VALUES (1 ,'CHILD101' ,1) GO INSERT INTO [PersonAttributeProcessStage_temp] ([PersonAttributeStageID] ,[AttributeValue] ,[ParentPersonAttributeProcessStageID]) VALUES (1 ,'CHILD102' ,1) GO INSERT INTO [PersonAttributeProcessStage_temp] ([PersonAttributeStageID] ,[AttributeValue] ,[ParentPersonAttributeProcessStageID]) VALUES (1 ,'Parent201' ,NULL) GO INSERT INTO [PersonAttributeProcessStage_temp] ([PersonAttributeStageID] ,[AttributeValue] ,[ParentPersonAttributeProcessStageID]) VALUES (1 ,'CHILD201' ,4) GO INSERT INTO [PersonAttributeProcessStage_temp] ([PersonAttributeStageID] ,[AttributeValue] ,[ParentPersonAttributeProcessStageID]) VALUES (1 ,'CHILD202' ,4) GO [1]: /storage/temp/1234-personattributestage.png
sql-server-2008t-sql
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is this and the previous post the same question?
0 Likes 0 ·
yes for some reason i am not able to view my previous post ,the post seems to be corrupted or displays blank.Couldnt delete it either.
0 Likes 0 ·
Looks like another moderator took care of it.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
You could simply use coalesce (ParentPersonAttributeProcessStageID, PersonAttributeProcessStageID) as the ParentChildGroupID column if you need a unique grouping id, but if it had to be contiguously ascending, then use dense_rank()over(order by coalesce (ParentPersonAttributeProcessStageID, PersonAttributeProcessStageID)) full query: SELECT *, coalesce (ParentPersonAttributeProcessStageID, PersonAttributeProcessStageID), dense_rank()over(order by coalesce (ParentPersonAttributeProcessStageID, PersonAttributeProcessStageID)) FROM PersonAttributeProcessStage_temp
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.