Try this:
DECLARE @cat TABLE (Cat_Id INT IDENTITY(1,1), Cat_Name NVARCHAR(30))
INSERT INTO @cat (Cat_Name)
VALUES('abc')
INSERT INTO @cat (Cat_Name)
VALUES('Kar')
INSERT INTO @cat (Cat_Name)
VALUES('Isb')
DECLARE @itm TABLE (Item_ID INT IDENTITY(1,1), Item_Name NVARCHAR(30), Cat_Id INT)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item1', 2)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item2', 1)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item3', 1)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item4', 3)
INSERT INTO @itm (Item_Name, Cat_Id)
VALUES('Item5', 2)
SELECT c.Cat_Name, i.Item_Name
FROM @cat AS c RIGHT JOIN @itm AS i ON c.Cat_Id = i.Cat_Id
ORDER BY c.Cat_Name
;WITH allitems AS
(
SELECT Cat_Id, '===' + Cat_Name + '===' AS [CategoriesAndItems], 0 As Item_Id
FROM @cat
UNION ALL
SELECT Cat_Id, Item_Name AS [CategoriesAndItems], Item_Id
FROM @itm
)
SELECT [CategoriesAndItems]
FROM allitems
ORDER BY Cat_id, Item_Id
SELECT statement returns:
abc Item2
abc Item3
Isb Item4
Kar Item5
Kar Item1
CTE returns:
===abc===
Item2
Item3
===Kar===
Item1
Item5
===Isb===
Item4
More:
Using Common Table Expressions[
^]
UNIONA (T-SQL)[
^]