Converting rows to columns is a common requirement in SQL Server when working with relational databases. This process is also known as "Pivoting". Pivoting is useful when you need to transform data from a table format with multiple rows and columns to a format with fewer columns and more rows.

Example

Here is an example of how to convert rows to columns in SQL Server. Let's assume we have a table called "EmployeeSales" with the following columns:

EmployeeID
SalesDate
SalesAmount

And the following data:

create table EmployeeSales
(
EmployeeID int,
SalesDate date,
SalesAmount int
);

insert into EmployeeSales(EmployeeID, SalesDate, SalesAmount)
values(1,'2022-01-01',1000), (1,'2022-02-01',2000), (1,'2022-03-01',3000);

insert into EmployeeSales(EmployeeID, SalesDate, SalesAmount)
values(2,'2022-01-01',4000), (2,'2022-02-01',5000), (2,'2022-03-01',6000);

To pivot this data and convert rows to columns, we can use the SQL Server PIVOT operator. The PIVOT operator transforms data from rows into columns, by rotating the unique values from one column into multiple columns in the output.

Here's an example of how to use the PIVOT operator to convert rows to columns in SQL Server:

SELECT EmployeeID, [2022-01-01], [2022-02-01], [2022-03-01]
FROM
(
SELECT EmployeeID, SalesDate, SalesAmount
FROM EmployeeSales
) es
PIVOT
(
SUM(SalesAmount)
FOR SalesDate IN ([2022-01-01], [2022-02-01], [2022-03-01])
) AS pivot_table;

This query will produce the following output:

In this example, the PIVOT operator is used to rotate the SalesDate values into columns, and the SalesAmount values are summed up for each combination of EmployeeID and SalesDate.

By using the PIVOT operator, you can easily convert rows to columns in SQL Server and transform data from a table format with multiple rows and columns to a format with fewer columns and more rows.