Transpose Query result in sql server

Question

I have a query that returns several rows (one to many relationship), I need to be able to obtain as a result a single row that has concatenated these values. This is my query:

SELECT I.[ID] 
 ,D.[CODEID] 
 ,D.[DESCRIPTION] 
 ,D.[PARENT]
 ,D.[LEVEL]
 FROM [wde].[INTERACCION] AS I
 LEFT JOIN [wde].[TIPIFICACION] AS t
 ON T.ID = I.ID
 INNER JOIN [wde].[DICCIONARIO] AS D
 ON D.CODIGOID = T.CODIGO

Result:

enter image description here

And I nedd this:

enter image description here

Note: The number of rows returned by the query are variables per record

Answer 1

I've created a quick model:

USE tempdb;
IF EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'wde')
BEGIN
 DROP TABLE wde.INTERACCION;
 DROP TABLE wde.TIPIFICACION;
 DROP TABLE wde.DICCIONARIO;
 DROP SCHEMA wde;
END
GO
CREATE SCHEMA wde;
GO
CREATE TABLE wde.INTERACCION (ID int);
CREATE TABLE wde.TIPIFICACION (ID int, CODIGO int);
CREATE TABLE wde.DICCIONARIO (CODEID int, [DESCRIPTION] varchar(100), [PARENT] varchar(100), [LEVEL] int, CODIGOID int);
INSERT INTO wde.INTERACCION (ID) VALUES (59);
INSERT INTO wde.TIPIFICACION (ID, CODIGO) VALUES (59,1);
INSERT INTO wde.DICCIONARIO(CODEID, DESCRIPTION,PARENT,LEVEL,CODIGOID) 
VALUES (100, 'MARCA VEHICULO' , 0 , 1, 1)
 , (101, 'SUCURSAL' , 0 , 1, 1)
 , (102, 'CONTACTADO' , 0 , 1, 1)
 , (105, 'FIAT' , 100, 2, 1)
 , (117, 'ECUADOR' , 101, 2, 1)
 , (122, 'CONTACTO TELEFONICO' , 102, 2, 1);

This query gets the results you want, but may only be valid for your limited set of test data shown. Without more specific requirements from you, it's difficult to be concise.

SELECT i.ID
 , Level1 = STUFF((SELECT ', ' + d.DESCRIPTION 
 FROM wde.DICCIONARIO d 
 INNER JOIN wde.TIPIFICACION t ON d.CODIGOID = t.CODIGO 
 WHERE t.ID = i.ID AND d.PARENT = 0 FOR XML PATH('')), 1, 2, '')
 , Level2 = STUFF((SELECT ', ' + d.DESCRIPTION 
 FROM wde.DICCIONARIO d 
 INNER JOIN wde.TIPIFICACION t ON d.CODIGOID = t.CODIGO 
 WHERE t.ID = i.ID AND d.PARENT <> 0 FOR XML PATH('')), 1, 2, '')
FROM wde.INTERACCION i;

Results:

╔════╦══════════════════════════════════════╦════════════════════════════════════╗
║ ID ║ Level1 ║ Level2 ║
╠════╬══════════════════════════════════════╬════════════════════════════════════╣
║ 59 ║ MARCA VEHICULO, SUCURSAL, CONTACTADO ║ FIAT, ECUADOR, CONTACTO TELEFONICO ║
╚════╩══════════════════════════════════════╩════════════════════════════════════╝