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

Question

I have two tables:

Departments --< Employees

Department table has data such as:

id, Name
1, Marketing
2, Sales

Employees table has data such as:

id, Name, DepartmentId, RatePerDay
1, Alex,1, 40
2, Bob,1, 30
3, Calvin,1, 40
4, Dal,1, 30

I want to get two data sets back as follows for each department:

DepartmentName, Employee1,Employee12,Employee13,Employee14
Marketing, Alex, Bob, Calvin, Dal

DepartmentName, RatePerDay1, RatePerDay2, RatePerDay3, RatePerDay4
Marketing, 40,30,40,30

I have to write a MS SQL 2008 Stored procedure which achieves this result?

Any help is appreciated

there are a lot of the same questions rows to columns tsql — sll
– sll, Commented Oct 27, 2011 at 13:18

Lamak · Accepted Answer · 2011-10-27 14:25:24Z

Ok, First take a look at this link, since you are gonna need dynamic SQL. Then you can try the following:

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)

xQbert · Accepted Answer · 2011-10-27 13:25:40Z

1

Pivot may work for what your doing:

http://msdn.microsoft.com/en-us/library/ms177410.aspx http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

answered Oct 27, 2011 at 13:25

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related