This will work for your sample data
New table definition
-- Add an IDENTITY ID column and a CategoryID column
CREATE TABLE books
(ID INT IDENTITY(1, 1) NOT NULL,
CategoryID INT NULL,
book_name VARCHAR(10),
code VARCHAR(3),
qty INT,
day VARCHAR(10));
-- We need the category ID to insert the books
DECLARE @Category INT;
-- First insert the category
insert into books values(null,'.Net',null,null,'x')
-- And fetch the new ID into our variable
SELECT @Category = SCOPE_IDENTITY();
-- Then insert the variable value into the category column for all the books
insert into books values(@Category,'Book1','NBC',2,'Mon')
insert into books values(@Category,'Book2','NAA',1,'Wed')
insert into books values(@Category,'Book3','NBA',3,'Tue/Mon')
-- Repeat for each category
insert into books values(null,'Oracle',null,null,'x')
SELECT @Category = SCOPE_IDENTITY();
insert into books values(@Category,'Book1','OB1',1,'D')
insert into books values(@Category,'Book2','OXZ',1,'Fri')
insert into books values(@Category,'Book3','OPS',2,'Sun')
insert into books values(@Category,'Book4','OPR',1,'Thu')
insert into books values(null,'Java',null,null,'x')
SELECT @Category = SCOPE_IDENTITY();
insert into books values(@Category,'Book1','JPS',1,'D')
insert into books values(@Category,'Book2','JJJ',4,'Sat')
insert into books values(null,'PHP',null,null,'x')
SELECT @Category = SCOPE_IDENTITY();
insert into books values(@Category,'Book1','PMW',3,'Wed/Tue')
insert into books values(@Category,'Book2','PHY',2,'D')
insert into books values(@Category,'Book3','PED',1,'Fri')
Query
-- Fetch todays day into a variable
DECLARE @Day CHAR(5) = '%' + SUBSTRING(DATENAME(DW, GETDATE()), 1, 3) + '%'
-- Load all categories into a temp table. Keep the ID column for sorting and add 0 for a row number also for sorting
SELECT
book_name, code, qty, NULL AS day, ID, 0 AS RowNbr
INTO
#t
FROM
books b
WHERE
day = 'x'
AND EXISTS(SELECT * FROM books b2 WHERE b2.CategoryID = b.ID AND (day = 'D' OR day LIKE @Day))
UNION ALL
-- Load all books into the same temp table. Use the CategoryID column for sorting and the ID for a row number
SELECT
book_name, code, qty, day, CategoryID, ID
FROM
books
WHERE
day = 'D' OR day LIKE @Day
-- Select your data sorted by category followed by row number
SELECT
book_name,
code,
qty,
day
FROM
#t
ORDER BY
ID,
RowNbr
DROP TABLE #t;
