mssql-Row to Column

Row-to-column is a frequently encountered problem in SQL, and is divided into static and dynamic transformations, so-called static conversions are known or fixed in the number of rows converted; Dynamic conversions are not fixed for the number of rows converted.

The method of conversion is generally implemented using case-when statements or pivot (MSSQL 2005 and later).

Let's start with the static transformation: Prepare the test data and generate it directly using the following SQL statement:

If Exists (Select * from sysobjects Where
id = object_id (' Sales ') and OBJECTPROPERTY (ID, ' isusertable ') = 1)
Begin
Drop Table Sales
End

Create Table Sales (
Name nvarchar (10),
Product nvarchar (12),
Quantity int)

Insert into Sales (name,product,quantity)
Values (' Leo ', ' Apple ', 300),
(' Leo ', ' Orange ', 23),
(' Amy ', ' Apple ', 432),
(' Amy ', ' Banana ', 45),
(' Leo ', ' Banana ', 74),
(' Tomy ', ' Apple ', 57),
(' Tomy ', ' Orange ', 92),
(' Tim ', ' Apple ', 76),
(' Tim ', ' Banana ', 45),
(' Tim ', ' Orange ', 159),
(' Amy ', ' Orange ', 610),
(' Alice ', ' Apple ', 245),
(' Alice ', ' Banana ', 342),
(' Alice ', ' Orange ', 138)
Go

After running, generate a sales table with the following data:

Now to count the number of sales by name as a record of the product name (product), or "NULL" if someone has not sold a product, the following format is generated:

The case and pivot solutions are as follows:

--case when scheme
Select Name,
SUM (case if product= ' apple ' then Quantity Else NULL End) as Apple,
SUM (case if product= ' Banana ' then Quantity Else NULL End) as Banana,
SUM (case if product= ' orange ' then Quantity Else NULL End) as Orange
From Sales
Group by Name

--pivot Scenario SQL 2005 and later
Select * from Sales
PIVOT
(
SUM (Quantity) for Product in (Apple,banana,orange)
) AS Pvt

The above is a static scheme, if one day the product added Strawberry,pear, and so on, if you want to achieve the results before, according to the above plan to manually modify the SQL statement, add two kinds of commodity columns, there is no one-way solution, the answer is of course, is dynamic SQL, As can be seen in the SQL statement above, if you can find all the goods in the case, and then loop to generate the box when the statement can be resolved; In a pivot solution, simply generate a string with "," attached to all the product records.

The specific implementation statements are as follows:

--case when scheme
Declare @sql varchar (8000)
Set @sql = ' Select Name '
Select @[email protected]+ ', sum (case when product= "+product+" then Quantity Else NULL End) as ' +product+ '
From (Select DISTINCT Product from Sales) as T--variable traversal assignment
Set @[email protected]+ ' from Sales Group by Name '
EXEC (@sql)

--pivot Scenario SQL 2005 and later
Declare @sql varchar (8000)
Set @sql = (Select DISTINCT ', ' +product from the Sales for XML PATH ("))
Set @sql =stuff (@sql, 1, 1, ")
Set @sql = ' Select * from Sales
PIVOT
(
SUM (Quantity) for Product in (' [email protected]+ ')
) AS Pvt '
EXEC (@sql)

Now feel free to add products can automatically generate the results we want.

mssql-Row to Column