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
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 183
Downloads 65
