1

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

2

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 ║
╚════╩══════════════════════════════════════╩════════════════════════════════════╝

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.