SQL Cross Apply String_Split with XML Path

SQL select statement to convert list of IDs found in column (from more than row or listed inside cell with comma separation) inside column, then find the name for each of these IDs from another table and list them in another column for each ID.
So, say we have 3 tables in SQL ...

Posts < Table1.jpg
PostID | PostName

Category (Cats in code example) < Table2.jpg
CatID | CatName

Settings (Alwa7Settings in code example) < Table3.jpg
ObjectID | SettingName | SettingValue
1stPostID | Post-x-CatIDs | ID1, ID2, ID3
2ndPost | Post-x-CatIDs | ID5
2ndPost | Post-x-CatIDs | ID6

What we want is to have in 1 table < Output.jpg
PostID | CategoryIDs | CategoryNames
1stPostID | ID1, ID2, ID3 | Cat1Name, Cat2Name, Cat3Name
2ndPost | ID5, ID6 | Cat5NAme, Cat6Name

As a bonus, select below also lists username for every post from a 4th table based on ID found in Posts table.

SQL below will do that, check screenshots

What is this?



Original Work
SELECT Top(60) PostID,
    PostCatsIDs= Stuff((
        Select ',' + Convert(nvarchar, SettingValue) FROM [nesrnet_main].[Alwa7Settings] AS S7 WHERE (ObjectID = P7.PostID) AND (SettingName = 'Post-x-CatIDs' )
        For XML Path('')), 1, 1, ''),
    PostCatsNames= Stuff((
        Select ',' + CatName From [Nesrnet_Main].[Cats] C7
        Where C7.CatID in (
            SELECT LTRim(RTrim(Convert(varchar, CS.Value))) As PostCatsIDs FROM [Nesrnet_Main].[Alwa7Settings] AS S7
            Cross Apply String_Split ( Convert(varchar, SettingValue), ',') CS
            WHERE (ObjectID = P7.PostID) AND (SettingName = 'Post-x-CatIDs' )
        For XML Path('')), 1, 1, ''),
    (Select UserFullName from [Nesrnet_Main].[Users] U7 Where U7.userid = PostUserID
    ) As PostUserName
    FROM    [Nesrnet_Main].[Posts] AS P7

Views 128

Downloads 33