Untitled

How do I achieve transposing of rows into columns in MS SQL

id, Name

1, Marketing

2, Sales

       

id, Name, DepartmentId, RatePerDay

1, Alex,1, 40

2, Bob,1, 30

3, Calvin,1, 40

4, Dal,1, 30

       

DepartmentName, Employee1,Employee12,Employee13,Employee14

Marketing, Alex, Bob, Calvin, Dal

 

DepartmentName, RatePerDay1, RatePerDay2, RatePerDay3, RatePerDay4

Marketing, 40,30,40,30

       

DECLARE @EmployeesId VARCHAR(MAX), @EmployeesIdAlias VARCHAR(MAX), @Query1 VARCHAR(MAX), @Query2 VARCHAR(MAX)

DECLARE @Rates VARCHAR(MAX), @RatesAlias VARCHAR(MAX)

 

SELECT  @EmployeesId = ISNULL(@EmployeesId + ',', '') + '[' + CAST(Id AS VARCHAR(10)) + ']',

        @EmployeesIdAlias = ISNULL(@EmployeesIdAlias + ',', '') + '[' + CAST(Id AS VARCHAR(10)) + '] AS [Employee ' + CAST(Id AS VARCHAR(10)) + ']',

        @RatesAlias = ISNULL(@RatesAlias + ',', '') + '[' + CAST(Id AS VARCHAR(10)) + '] AS [Rate ' + CAST(Id AS VARCHAR(10)) + ']'

FROM Employees

 

 

SET @Query1 = '

SELECT Department, '+@EmployeesIdAlias+'

FROM (  SELECT A.Id, A.Name, B.Name Department

        FROM Employees A

        INNER JOIN Department B

        ON A.DepartmentId = B.Id) Source

PIVOT(MIN(Name) FOR Id IN ('+@EmployeesId+')) AS PT'

 

EXEC(@Query1)

 

SET @Query2 = '

SELECT Department, '+@RatesAlias+'

FROM (  SELECT A.Id, A.RatePerDay, B.Name Department

        FROM Employees A

        INNER JOIN Department B

        ON A.DepartmentId = B.Id) Source

PIVOT(MIN(RatePerDay) FOR Id IN ('+@EmployeesId+')) AS PT'

 

EXEC(@Query2)