SQL Server Project 1

PROBLEM:

we have a table named as CONSULTANTS. And it has some data with id, name and expertise fields. Write a SQL query which could give the following output:

Original table output	Expected output

SOLUTION:

Step 1: creation of the table

CREATE TABLE CONSULTANTS(

  id NVARCHAR(10),

  name NVARCHAR(20),

  expertise NVARCHAR(20)

);

Step 2: inserting some records into the table

INSERT INTO CONSULTANTS VALUES('001', 'Mr. B', 'SSRS');

INSERT INTO CONSULTANTS VALUES('002', 'Mr. R', 'SSRS');

INSERT INTO CONSULTANTS VALUES('001', 'Mr. B', 'SSIS');

INSERT INTO CONSULTANTS VALUES('003', 'Mr. K', 'DBA');

INSERT INTO CONSULTANTS VALUES('004', 'Mr. M', 'Nothing');

Step 3: Checking the output of the table

SELECT * FROM CONSULTANTS;

Step 4: Writing the code to get the expected output

SELECT

  result.id [Consultant ID]

  ,result.name [Consultant Name]

  ,iif(result.SSRS = 1, 'Yes', '') SSRS

  ,iif(result.SSIS = 1, 'Yes', '') SSIS

  ,iif(result.DBA = 1, 'Yes', '') DBA

  ,iif(result.Nothing = 1, 'Yes', '') Nothing

FROM(

      SELECT

          *

      FROM

          CONSULTANTS

   PIVOT(

          COUNT(expertise)

          FOR expertise IN ([SSRS], [SSIS], [DBA], [Nothing])

   ) AS PIVOTRESULT

) result

And after executing the above query, it will throw the following output.

Good luck  :D