CODEWITHSUNDEEP
sqlsql-server
user1301587
user1301587

Reputation: 455

SQL Server output in desired formatted way

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]

enter image description here

tblCode:

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

enter image description here

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.

enter image description here

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')

Upvotes: 0

Views: 50

Answers (2)

Robert Bain
Robert Bain

Reputation: 9576

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

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 2

Related Questions