SQL query JOIN, many-to-many-CodePudding

I need small assistance in order to get one query(output) from these tables:

table content

UUID	Type	CatUUID
ZX5N	image	AB6S
ZX5N	image	AB5S
ZX5N	doc	TID5
ZX5N	doc	TID6

table image

UUID	Image
AB6S	test1
AB5S	test2

table doc

UUID	Doc
TID5	test3
TID6	test4

table text

UUID	Body	Desc
ZX5N	text1	text2

the output I want is from text.Body, text.Desc and image.Image

So something like these:

OUTPUT:

UUID	BODY	Desc	Image	Doc
ZX5N	text1	text2	test1,test2	test3,test4

In use is MSSQL 2019

I tried to get only "image" first but failed and couldn't pass that, and have no idea how to then add everything in same row with "comma" seperated as seen from the required output

SELECT text.UUID,text.Body, text.Desc, image.Image
FROM text
LEFT OUTER JOIN content.UUID on text.UUID AND content.Type = 'image'
LEFT OUTER JOIN image on content.CatUUID  =  image.UUID

CodePudding user response：

You can use STRING_AGG() for this:

SELECT t.UUID, t.Body, t.[Desc],
  Image = STRING_AGG(i.[Image], ','),
  Doc   = STRING_AGG(d.Doc, ',')
FROM dbo.[text] AS t
LEFT OUTER JOIN dbo.content AS c
  ON t.UUID = c.UUID
LEFT OUTER JOIN dbo.[image] AS i
  ON c.[Type] = 'image' AND c.CatUUID = i.UUID
LEFT OUTER JOIN dbo.doc AS d
  ON c.[Type] = 'doc' AND c.CatUUID = d.UUID
GROUP BY t.UUID, t.Body, t.[Desc];

Working example in this fiddle.

Also, try to use [fewer] [reserved] [words], which require delimiting that makes them [harder] [to] [read].