The table structure and data of a table in the SQL database is like below.
| EmpId | EmpName | Skills | | | | | | 1 | ABC | ASP.NET | | 1 | ABC | C# | | 1 | ABC | SQL | | 1 | ABC | Javascript | | 2 | XYZ | ASP.NET | | 2 | XYZ | VB.NET | | 2 | XYZ | Javacript | | 2 | XYZ | IIS | | 3 | PQR | VB.NET | | 3 | PQR | C# | | 3 | PQR | SQL |
The requirement is to retrieve the skillset of an employee based on the emplid.
| EmpId | EmpName | SkillSet | | | | | | 1 | ABC | ASP.NET, C#, SQL, Javascript |
The above result can be get using cursor but the cursor is always performance hit. We can get the above result without using cursor also. Step 1: Declare a table variable with column empId, empName, skills and insert few set of records
DECLARE @temp Table(empId INT, empName VARCHAR(50), skills VARCHAR(20))
INSERT INTO @temp(empId, empName, skills) VALUES(1,'ABC','ASP.NET')
INSERT INTO @temp(empId, empName, skills) VALUES(1,'ABC','C#')
INSERT INTO @temp(empId, empName, skills) VALUES(1,'ABC','SQL')
INSERT INTO @temp(empId, empName, skills) VALUES(1,'ABC','Javascript')
INSERT INTO @temp(empId, empName, skills) VALUES(2,'XYZ','ASP.NET')
INSERT INTO @temp(empId, empName, skills) VALUES(2,'XYZ','VB.NET')
INSERT INTO @temp(empId, empName, skills) VALUES(2,'XYZ','Javascript')
INSERT INTO @temp(empId, empName, skills) VALUES(2,'XYZ','IIS')
INSERT INTO @temp(empId, empName, skills) VALUES(3,'PQR','VB.NET')
INSERT INTO @temp(empId, empName, skills) VALUES(3,'PQR','C#')
INSERT INTO @temp(empId, empName, skills) VALUES(3,'PQR','SQL')
Step 2: Below lines of code will cocatenate the skills of an employee based on the empId in where clause, without using cursor
DECLARE @empId INT,@empName VARCHAR(50), @skillset VARCHAR(100)
SET @skillset = '' SELECT @empId = empId, @empName = empName, @skillset = CASE @skillset WHEN ' THEN skills ELSE @skillset + ', ' + skills END FROM @temp WHERE empId = 3SELECT @empId AS EmpId, @empName AS EmpName, @skillset AS SkillSet
or We can use COALESCE also (I modified this article and put below option after Vikram's comment, thanks Vikram)DECLARE @empId INT,@empName VARCHAR(50), @skillset VARCHAR(100)
SET @skillset = ''
SELECT @empId = empId, @empName = empName, @skillset = COALESCE(@skillset,') + skills + ',' FROM @temp WHERE empId = 3SELECT @empId AS EmpId, @empName AS EmpName, @skillset AS SkillSet
Both the queries returned same resulset.
|