我该如何转动这个(How can I pivot this)

我试图转动一些信息，但无法解决它。请帮我。我正在使用SQL Express 2012.我写了这个例子：

CREATE TABLE #temp ( Mes varchar(2), Qty int, Modelo varchar(50) ) insert into #temp values('01', 11, 'TC') insert into #temp values('01', 21, 'TC2') insert into #temp values('01', 22, 'Plus2') insert into #temp values('02', 12, 'TC') insert into #temp values('02', 22, 'TC2') insert into #temp values('02', 32, 'Plus2') insert into #temp values('03', 13, 'TC') insert into #temp values('03', 23, 'TC2') insert into #temp values('03', 33, 'Plus2') SELECT Modelo, [AAA] as Mes1, [BBB] as Mes2, [CCC] as Mes3 from ( select Mes, Modelo, Qty from #temp ) x PIVOT ( SUM(Qty) FOR Mes IN([AAA], [BBB], [CCC]) ) as p drop table #temp

结果是：

我这里有2个问题，第一个没有导致交集模型（行中的Modelo）和列（aaa，bbb，ccc）中的月（Mes）

第二个是如何直接将月份（Mes）名称放在列中而不使用Mes1等。

感谢您的任何帮助。

I'm trying to pivot some info and can't resolve it. Please help me. I'm using SQL Express 2012. I wrote this example:

CREATE TABLE #temp ( Mes varchar(2), Qty int, Modelo varchar(50) ) insert into #temp values('01', 11, 'TC') insert into #temp values('01', 21, 'TC2') insert into #temp values('01', 22, 'Plus2') insert into #temp values('02', 12, 'TC') insert into #temp values('02', 22, 'TC2') insert into #temp values('02', 32, 'Plus2') insert into #temp values('03', 13, 'TC') insert into #temp values('03', 23, 'TC2') insert into #temp values('03', 33, 'Plus2') SELECT Modelo, [AAA] as Mes1, [BBB] as Mes2, [CCC] as Mes3 from ( select Mes, Modelo, Qty from #temp ) x PIVOT ( SUM(Qty) FOR Mes IN([AAA], [BBB], [CCC]) ) as p drop table #temp

The result for this is:

I have 2 problems here, first one no result in the intersection Model (Modelo in rows) and Month (Mes) in columns (aaa, bbb, ccc)

The second one, is how to put the month (Mes) name in the column directly without using as Mes1, etc.

Thank you for any help.

最满意答案

您在Mes列中插入（'01'，'02'，'03'）。像'AAA'，'BBB'和'CCC'这样的值是无效值，将返回null。所以你必须改变你的代码如下：

CREATE TABLE #temp ( Mes varchar(2), Qty int, Modelo varchar(50) ) insert into #temp values('01', 11, 'TC') insert into #temp values('01', 21, 'TC2') insert into #temp values('01', 22, 'Plus2') insert into #temp values('02', 12, 'TC') insert into #temp values('02', 22, 'TC2') insert into #temp values('02', 32, 'Plus2') insert into #temp values('03', 13, 'TC') insert into #temp values('03', 23, 'TC2') insert into #temp values('03', 33, 'Plus2') SELECT Modelo, [01] as Mes1, [02] as Mes2, [03] as Mes3 from ( select Mes, Modelo, Qty from #temp ) x PIVOT ( SUM(Qty) FOR Mes IN([01], [02], [03]) ) as p drop table #temp

它将返回：

Modelo | Mes1 | Mes2 | Mes3 --------+-------+-------+----------- Plus2 | 22 | 32 | 33 TC | 11 | 12 | 13 TC2 | 21 | 22 | 23

You inserted ('01', '02', '03') in Mes column. Values like 'AAA', 'BBB' and 'CCC' are invalid values and will return null. So you must chenge your code as follow:

CREATE TABLE #temp ( Mes varchar(2), Qty int, Modelo varchar(50) ) insert into #temp values('01', 11, 'TC') insert into #temp values('01', 21, 'TC2') insert into #temp values('01', 22, 'Plus2') insert into #temp values('02', 12, 'TC') insert into #temp values('02', 22, 'TC2') insert into #temp values('02', 32, 'Plus2') insert into #temp values('03', 13, 'TC') insert into #temp values('03', 23, 'TC2') insert into #temp values('03', 33, 'Plus2') SELECT Modelo, [01] as Mes1, [02] as Mes2, [03] as Mes3 from ( select Mes, Modelo, Qty from #temp ) x PIVOT ( SUM(Qty) FOR Mes IN([01], [02], [03]) ) as p drop table #temp

It will return:

Modelo | Mes1 | Mes2 | Mes3 --------+-------+-------+----------- Plus2 | 22 | 32 | 33 TC | 11 | 12 | 13 TC2 | 21 | 22 | 23

更多推荐