0

I currently have a view in SQL Server, something like this:

Table1:

Id
Desc
Mex

Table2:

Id
IdTab1
Desc

The view select everything from Table1 left joined on Table2 on Id - IdTab1

Now I have a table 3 joined with Table2 that has like these fields:

Table3:

Id
IdTab2
Code (VarChar(3))

I would like to have in the select of the view a new field Code that contains every code in table 3 concatenated with the char ' ' without changing the record displayed from the old query (so like doing a group by concat) every Code that matches the join.

I saw some other posts but neither of them used this kind of approach. For example using this:

declare @result varchar(500)
set @result = ''
select @result = @result + ModuleValue + ', ' 
from TableX where ModuleId = @ModuleId

But I have faced two problems. I could not use declare in the view (probably because of wrong syntax), and also I have to do this group by and I can't figure out how.

Example result basic view

ID | IDTAB2 | DESC1 | DESC2 | MEX

1  |   2    |   aa  |  bb   |  4
2  |   1    |   ab  |  cc   |  2
2  |   2    |   bb  |  bc   |  2

Example result joined Table3

ID | IDTAB2 | DESC1 | DESC2 | MEX | CODE 

1  |   2    |   aa  |  bb   |  4  |  CS
1  |   2    |   aa  |  bb   |  4  |  NN
2  |   1    |   ab  |  cc   |  2  |  AF
2  |   2    |   bb  |  bc   |  2  |  DC
2  |   2    |   bb  |  bc   |  2  |  KK
2  |   2    |   bb  |  bc   |  2  |  JD

Example result needed

ID | IDTAB2 | DESC1 | DESC2 | MEX | CODENEW

1  |   2    |   aa  |  bb   |  4  | CS NN
2  |   1    |   ab  |  cc   |  2  | AF
2  |   2    |   bb  |  bc   |  2  | DC KK JD
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Zannabrighel
  • 1
  • 1
  • Does this answer your question? [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Wouter Jul 24 '20 at 15:12
  • Share your current query. It will be helpful to fix the issues – Venkataraman R Jul 24 '20 at 15:24
  • @VenkataramanR should i even if it is really big? There are too many (not relevant) tables involved. – Zannabrighel Jul 24 '20 at 15:53
  • @Wouter Thanks, i'm trying with this (but probably is too slow). I'm sorry because i havn't seen this post while i was trying to solve this problem by myself – Zannabrighel Jul 24 '20 at 15:54

1 Answers1

0

Considering your output from "Example result joined Table3", you can try this below option based on your SQL server version-

For MSSQL-2016 and earlier- Demo

SELECT DISTINCT A.ID,A.IDTAB2,A.DESC1,A.DESC2,A.MEX, 
SUBSTRING(
    (
        SELECT ' '+ B.CODE  AS [text()]
        FROM your_table B
        WHERE B.ID = A.ID
        AND B.IDTAB2 = A.IDTAB2
        AND B.DESC1 = A.DESC1
        AND B.DESC2 = A.DESC2
        AND B.MEX = A.MEX
        ORDER BY B.ID,B.IDTAB2,B.DESC1,B.DESC2,B.MEX
        FOR XML PATH ('')
    ), 2, 1000) [C_Name]
FROM your_table A

For MSSQL-2017 or newer- Demo

SELECT ID,IDTAB2,DESC1,DESC2,MEX,
STRING_AGG ( CODE, ' ' )
FROM your_table
GROUP BY ID,IDTAB2,DESC1,DESC2,MEX
mkRabbani
  • 16,295
  • 2
  • 15
  • 24