mssql-Row to Column

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.