我正在寻找在SQL Server 2012中将行转换为列的方法 [英] I'm looking for way to convert rows to columns in SQL server 2012

问题描述

这是我的例子：

This is my example:

Date        Itemname      qty     price 
01/09/2016  ABC           100     1000
02/09/2016  PQR           200     500
02/09/2016  ABC           50      500
04/09/2016  XYZ           10      100
05/09/2016  ABC           50      500

我想要结果：

I want result:

Date             ABC           PQR           XYZ
             qty  price     qty  price      qty  price

01/09/2016   100  1000      
02/09/2016   50   500       200   500       
04/09/2016   50   100                       10    100

产品名称是没有修复。我有一个未知数量的项目名称。



我尝试过：

Item names are not fix.I have an unknown number of Item names.

What I have tried:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

推荐答案

继续解决方案1以及对它的评论...



首先要做的是将结果转换为您在示例中显示的固定数据所需的格式。



一这样做的方法是首先取消你的数据，然后使用虚拟列为每个ItemName的数量和价格进行PIVOT，例如

Following on from Solution 1 and your comment to it...

The first thing to do is to get the results into the format you require for the fixed data you have shown in your example.

One way of doing that would be to UNPIVOT your data first and then PIVOT using a dummy column for the qty and price for each ItemName e.g.

;with CTE as
(
	SELECT * FROM 
		(SELECT [Date], qty, price, itemname
		FROM yourTable) p
		UNPIVOT
	   (value FOR what IN (qty, price)
	)AS unpvt
)
select [Date],	ABCqty,ABCprice,
		PQRqty,PQRprice,
		XYZqty,XYZprice
from
(
	select [Date],value, ItemName + what as itemwhat
	from CTE
) qry
pivot 
(
	max(value)
    for itemwhat in ([ABCqty],[PQRqty],[XYZqty],[ABCprice],[PQRprice],[XYZprice])
) as pvt
order by [Date]

一旦你知道它正在运行，那么使用您已经在问题的我尝试过的部分中使用过的技术 - 根据表的实际数据内容而不是硬编码的列名动态生成SQL - 这是一个片段（我会留下其余部分）

Once you know that is working then use the technique you have already used in the "What I have tried" section of your question - generate the SQL dynamically based on the actual data content of the table instead of the hard-coded column names - here is a snippet of that (I'll leave the rest to you)

select @cols = STUFF((SELECT ',' + QUOTENAME(ItemName + 'qty') +  ',' + QUOTENAME(ItemName + 'price') 
    from yourtable
    group by ItemName
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

如果您希望它与预期结果一样精确显示，那么您应该在表示层中执行此操作，而不是查询本身

If you want it displayed precisely as you have in your expected results then you should do that in your presentation layer, not the query itself

Pivot可能是您正在寻找的：

在SQL查询中使用Pivot的简单方法 [ ^ ]

Sql Server中的PIVOT和UNPIVOT | SqlHints.com [ ^ ]

使用PIVOT和UNPIVOT [ ^ ]

Pivot is probably what youa re looking for:
Simple Way To Use Pivot In SQL Query[^]
PIVOT and UNPIVOT in Sql Server | SqlHints.com[^]
Using PIVOT and UNPIVOT[^]

这篇关于我正在寻找在SQL Server 2012中将行转换为列的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！