SQL Server output in desired formatted way

Question

I have two tables, tblName and tblCode.

tblName:

CREATE TABLE [dbo].[TblName]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,

    CONSTRAINT [PK_TblName] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]

tblCode:

CREATE TABLE [dbo].[tblCode]
(
    [NameId] [int] NULL,
    [Code] [varchar](50) NULL,
    [Value] [varchar](50) NULL
) ON [PRIMARY]

Code:

INSERT INTO [dbo].[TblName]
   ([Name])
VALUES
   ('Rahul'),
   ('Rohit'),
   ('John'),
   ('David'),
   ('Stephen')
   GO

INSERT INTO [dbo].[tblCode] ([NameId], [Code], [Value])
VALUES (1, 'DEL', 'Delivery'),
       (1, 'DEL', 'Deployment'),
       (2, 'REL', 'Release Management'),
       (3, 'REL', 'Release Management'),
       (4, 'TEST', 'Testing'),
       (4, 'TEST', 'Final Testing')

I am trying to write a query to get all Names which are in tblCode with the Code and Value. For example I have NameId 1 present in tblCode with Code 'DEL' and Value as 'Delivery' and 'Deployment'. Similarly I have NameId 2,3 and 4 in tblCode with same or different Code and Value. So I am trying to get output in such a way if same name with same code is present in tblCode then it should come row with Name and Comma separated values as shown in below desired output.

This is they query I am using but its not giving the output I am looking for.

 SELECT  
     N.Name,                
     CASE 
        WHEN C.Code = 'DEL' 
           THEN C.Value 
        ELSE '' 
     END As 'CodeValue'     
 FROM 
     TblName N
 INNER JOIN 
     tblCode C ON N.Id = C.NameId
 WHERE
     C.NameId = 1 AND C.Code IN ('DEL', 'REL', 'TEST')

Please edit your question and change the images to ddl+dml for sample data and formatted text for desired results. BTW, what if you have two recrods with the same name id but different code? Should they appear as different rows in the output? — Zohar Peled, Jul 23 '17 at 11:47
Yes, they should appear in different rows as the condition should be if Same Name + Same Code is more than 1 records then show the CodeValues in comma separated. — user1301587, Jul 23 '17 at 11:56

score 2 · Answer 1 · answered Jul 23 '17 at 12:22

Here is one way to do it:

;WITH CTE AS
(
SELECT DISTINCT
        [NameId]
       ,[Code]
       ,(
            SELECT STUFF(
                (SELECT ',' + Value
                FROM dbo.tblCode t1
                WHERE t0.Code = t1.Code
                AND t0.NameId = t1.NameId
                FOR XML PATH(''))
                , 1, 1, '')
        ) AS CodeValue
FROM dbo.tblCode t0
)

SELECT  Name, CodeValue
FROM tblName 
INNER JOIN CTE ON Id = CTE.NameId
ORDER BY Id

Results:

Name    CodeValue
Rahul   Delivery,Deployment
Rohit   Release Management
John    Release Management
David   Testing,Final Testing

Read this SO post for an explanation on how to use STUFF and FOR XML to create a concatenated string from multiple rows.

You can see a live demo on rextester.

Thank you so much Zohar. One question though is it possible to use COALESCE for same output? — user1301587, Jul 23 '17 at 12:32
You could use coalesce to concatenate multiple rows to a single row, but for this you will need somewhere to store it, which will result in a more complicated code that performs worst. Read [this article](https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) for more information. — Zohar Peled, Jul 23 '17 at 12:44

Robert Bain · Accepted Answer · 2017-07-23T22:45:34.920

http://sqlfiddle.com/#!6/bdca78/11/0

CREATE TABLE tblName (id INTEGER, name VARCHAR(255));

INSERT INTO tblName VALUES(1, 'Rahul');
INSERT INTO tblName VALUES(2, 'Rohit');
INSERT INTO tblName VALUES(3, 'John');
INSERT INTO tblName VALUES(4, 'David');
INSERT INTO tblName VALUES(5, 'Steven');

CREATE TABLE tblCode(nameId INTEGER, code VARCHAR(255), value VARCHAR(255));

INSERT INTO tblCode VALUES(1, 'DEL', 'Delivery');
INSERT INTO tblCode VALUES(1, 'DEL', 'Development');
INSERT INTO tblCode VALUES(2, 'REL', 'Release Management');
INSERT INTO tblCode VALUES(3, 'REL', 'Release Management');
INSERT INTO tblCode VALUES(4, 'TEST', 'Testing');
INSERT INTO tblCode VALUES(4, 'TEST', 'Final Testing');

SELECT name,
       codeValue
FROM
(SELECT tblName.name AS name,
       STUFF((SELECT  ',' + tblCode.value 
              FROM tblCode
              WHERE tblCode.nameId = tblName.id 
              FOR XML PATH('')), 1 ,1, '') AS codeValue
FROM tblName) inline_view
WHERE codeValue IS NOT NULL;

Edit

ZoharPeled's solution is correct. This solution returns the name and a comma separated list of value and does not consider the code.

Zohar aggregates the list of value per code, which I think is what's required. OP, if the objective is to aggregate per name per code, including code in the output would make the result set more meaningful.

The following links show the difference, first is my SQL statement, then Zohar's.

My SQL: http://sqlfiddle.com/#!6/94fd0/1/0 Zohar's SQL: http://sqlfiddle.com/#!6/94fd0/2/0

While on a first glance it looks simpler then my answer, it would not procude different rows for different codes with the same name id, so it will work for this sample data but not if the same name id have multiple codes attached to it. — Zohar Peled, Jul 23 '17 at 12:38
@ZoharPeled, I agree. If that's a requirement, your solution is the one to go with. — Robert Bain, Jul 23 '17 at 13:00
According to the conversation I've had with the OP in the comments, that's a requirement. — Zohar Peled, Jul 23 '17 at 13:51
Zohar, I agree. I've updated my submission to reflect our conversation. OP should mark your post as the correct answer. — Robert Bain, Jul 23 '17 at 22:14

SQL Server output in desired formatted way

2 Answers2