Sorry I know it's spoon feeding already. I've exerted all efforts but cant find the right script. I was thinking to put a "NULL" value to the columns that lacks so that i could be able to union. but the problem is i dont know where. :sick:

this is the part that i dont really understand. specially to the part where you put ".value". if you could walk me through on what it does that would be a GREAT HELP.

STUFF((SELECT CHAR(13)+CHAR(10)+'Union All SELECT '+Dyn

FROM (SELECT [book]

FROM CTE

GROUP BY [book]

)a

CROSS APPLY (SELECT STUFF((SELECT ','+CHAR(13)+CHAR(10)+

CHAR(39)+ b.[Category_Name]+CHAR(39)+ ' AS ' +

CASE WHEN b.[book] = b.[Category_Name] THEN '[Book]'

ELSE '[Level ' +CAST(b.[Pos] AS VARCHAR(3))+']'

END

FROM CTE b

WHERE a.[book] = b.[book]

ORDER BY [book], [Pos]

FOR XML PATH(''), TYPE

).value('.','NVARCHAR(MAX)'),1,1,''

)

)ca(Dyn)

FOR XML PATH(''), TYPE

).value('.','NVARCHAR(MAX)'),1,12,''

);