Parthiban Kannan Parthiban Kannan - 2 months ago 6
SQL Question

Is it Possible to get the multiple set result into single set of result in SQL Server?

My SQL table looks like this

CREATE TABLE [CONTENTS].[ID]
(
[ID] NVARCHAR (20) NOT NULL,
[NAME] NVARCHAR (MAX) NOT NULL,
[CONTENT] NVARCHAR (MAX) NULL,
[PARENT_ID] NVARCHAR (20) NOT NULL,
[TYPE] INT NOT NULL,
[SHARED] INT NOT NULL,
[CREATED] DATETIME NULL,
[ICON] VARBINARY (MAX) NULL,
[UPDATED] DATETIME NULL
);


Every item has the parent item except some major items. I need to get for required item Parent, Parent of Parent, until the item having no parent..

I used the following query

DECLARE @ID nvarchar(max)='12843686753443770653';

WAY:
SET @ID = (SELECT PARENT_ID FROM CONTENTS.ID WHERE (ID = @ID))

SELECT *
FROM CONTENTS.ID
WHERE (ID = @ID)

IF @ID!='JKParthiban' GOTO WAY


I got this result:

enter image description here

I need all the results in a single set instead of multiple sets.

Answer

You can use recursive cte to get the results

DECLARE @ID nvarchar(max)='12843686753443770653';
;with cte as (
select id, name from contentsid where id = @ID

union all

select ci.ID, ci.name from cte c inner join contentsid ci
on c.id = ci.PARENT_ID
) select * from cte
Comments