Sql Query - One to Many

Question

I have three tables in my database.

1. Employees
2. Skill
3. EmployeeSkill

Here is the structure

I am unable to think of a query that would return me results in the following format

Employee    [Skill1]    [Skill2]    [Skill3]  ...

Mr.Abc      true         false       true

Mr.Xyz      false        true        true

Where [Skill1], [Skill2] etc will list all the skills for a specific category defined inside skill table and the column value (true/false) will depend on records in EmployeeSkill table. For example if there is an entry in the table that links Employee with [skill1], it would list true and if there is no entry it will list false.

Additionally the number of skill selected (and displayed as column headers) can change based on the the skillCategory.

Help appriciated

Answer 1

Use SQL PIVOT function

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

example from that site:

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
   FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

See also this answer for a decent example on pivot: How to create a pivot query in sql server without aggregate function

UPDATE: I've created a SQL Fiddle for you, showing how to do it. The only issue is that with PIVOT, you cannot make the columns dynamic. There is a way to make it dynamic, but only by using dynamic queries... (there is here an example showing how: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx -> comment 'Como hacer PIVOT con consultas dinámicas').

This is the SQL Fiddle i made for you: http://sqlfiddle.com/#!3/cb979/5/1

Assuming this base code:

CREATE TABLE EMPLOYEES
    (
    Id int NOT NULL IDENTITY (1, 1),
    FirstName varchar(50) NULL,
    LastName varchar(50) NULL,
    Salary float(53) NULL,
    Department varchar(50) NULL
    )  ON [PRIMARY]

CREATE TABLE Skill
    (
    Id int NOT NULL IDENTITY (1, 1),
    Skill varchar(50) NULL,
    )  ON [PRIMARY]

CREATE TABLE EmployeeSkill
    (
    SkillId int,
    EmployeeId varchar(50) NULL,
    ) 

INSERT INTO EMPLOYEES (FirstName, LastName, Salary, Department) 
  VALUES ('Alex','T',200,'IT');
INSERT INTO EMPLOYEES (FirstName, LastName, Salary, Department) 
  VALUES ('Zed','Bee',300,'IT');

INSERT INTO Skill (Skill) VALUES ('SQL Skill');
INSERT INTO Skill (Skill) VALUES ('HTML Skill');
INSERT INTO Skill (Skill) VALUES ('PHP Skill');

INSERT INTO EmployeeSkill (SkillID, EmployeeID) VALUES(1,1);
INSERT INTO EmployeeSkill (SkillID, EmployeeID) VALUES(2,1);
INSERT INTO EmployeeSkill (SkillID, EmployeeID) VALUES(3,1);
INSERT INTO EmployeeSkill (SkillID, EmployeeID) VALUES(1,2);

And this SQL to create the pivot:

SELECT *
FROM
(
SELECT EmployeeID, FirstName + ' ' + LastName as FullName, SkillID, Skill
FROM EmployeeSkill LEFT JOIN Skill ON Skill.ID = SkillID
  LEFT JOIN Employees ON Employees.ID = EmployeeID
) AS source
PIVOT
(
    COUNT([SkillID])
    FOR [Skill] IN ([SQL Skill], [HTML Skill], [PHP Skill])
) as pvt;