SQL-枢转多个列而没有聚集 [英] SQL - Pivot multiple columns without Aggregrates
问题描述
我不确定如何将数据透视到特定视图.以下是测试数据.
I am unsure how to pivot my data to the a specific view. Below is the test data.
SQL
CREATE TABLE #tmpData (ProductTitle VARCHAR(100), abvrMonthName VARCHAR(3),abvrMonthNameCount VARCHAR(4),MonthAvg NUMERIC(6,2),MonthCount INT)
INSERT INTO #tmpData SELECT 'Product 1','Dec','Dec#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 1','Nov','Nov#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 1','Oct','Oct#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 1','Sep','Sep#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 1','Aug','Aug#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 1','Jul','Jul#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 1','Jun','Jun#', 5 , 1
INSERT INTO #tmpData SELECT 'Product 1','May','May#', 4.44 , 9
INSERT INTO #tmpData SELECT 'Product 1','Apr','Apr#', 5 , 6
INSERT INTO #tmpData SELECT 'Product 1','Mar','Mar#', 5 , 4
INSERT INTO #tmpData SELECT 'Product 1','Feb','Feb#', 5 , 1
INSERT INTO #tmpData SELECT 'Product 1','Jan','Jan#', 5 , 2
INSERT INTO #tmpData SELECT 'Product 2','Dec','Dec#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Nov','Nov#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Oct','Oct#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Sep','Sep#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Aug','Aug#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Jul','Jul#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Jun','Jun#', 5 , 1
INSERT INTO #tmpData SELECT 'Product 2','May','May#', 4.67 , 3
INSERT INTO #tmpData SELECT 'Product 2','Apr','Apr#', 4.33 , 3
INSERT INTO #tmpData SELECT 'Product 2','Mar','Mar#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Feb','Feb#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 2','Jan','Jan#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 3','Dec','Dec#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 3','Nov','Nov#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 3','Oct','Oct#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 3','Sep','Sep#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 3','Aug','Aug#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 3','Jul','Jul#', 0 , 0
INSERT INTO #tmpData SELECT 'Product 3','Jun','Jun#', 5 , 3
INSERT INTO #tmpData SELECT 'Product 3','May','May#', 5 , 6
INSERT INTO #tmpData SELECT 'Product 3','Apr','Apr#', 4 , 6
INSERT INTO #tmpData SELECT 'Product 3','Mar','Mar#', 4.75 , 8
INSERT INTO #tmpData SELECT 'Product 3','Feb','Feb#', 4.75 , 8
INSERT INTO #tmpData SELECT 'Product 3','Jan','Jan#', 4.6 , 5
SELECT ProductTitle,[jan],[jan#],[feb],[feb#]
FROM
( SELECT *
FROM #tmpData
) AS s
PIVOT
(
SUM(MonthAvg) FOR abvrMonthName IN (
jan,feb,mar,apr,may,jun,jul,aug, sep, oct, nov, [dec]
)
) as p
PIVOT
(
SUM(MonthCount) FOR abvrMonthNameCount IN (
jan#,feb#,mar#,apr#,may#,jun#,jul#,aug#, sep#, oct#, nov#, [dec#]
)
) as p1
--GROUP BY ProductTitle,[jan],[feb]
DROP TABLE #tmpData
从输出中可以看到，ProductTitle未分组.
As you can see from the output the ProductTitle is not grouped.
我将如何实现这一目标?或者我完全朝错误的方向前进?
How would I achieve this or am I going in the wrong direction completely?
推荐答案
部分问题是您要对要遍历的多个列进行了非规范化的数据处理.理想情况下，您应该考虑修复表结构，以便于维护和查询.如果无法修复表结构，则应先取消透视列，然后应用PIVOT以获得最终结果.
Part of the problem is that you have de-normalized data across multiple columns that you want to pivot. Ideally, you should consider fixing your table structure so you it will be easier to maintain and query. If you are not able to fix the table structure, then you should unpivot the columns first to then apply PIVOT to get the final result.
UNPIVOT进程将采用多列并将其转换为多行.根据您的SQL Server版本，有几种方法可以执行此操作.可以使用UNPIVOT函数，或者由于使用的是SQL Server 2008，因此可以将CROSS APPLY与VALUES子句一起使用来取消透视.
The UNPIVOT process will take the multiple columns and convert them into multiple rows. Depending on your version of SQL Server there are a few ways that you can do this. You can use the UNPIVOT function or since you are using SQL Server 2008, you can use CROSS APPLY with the VALUES clause to unpivot.
CROSS APPLY/VALUES代码为:
The CROSS APPLY/VALUES code will be:
select t.producttitle, c.col, c.value
from tmpData t
cross apply
(
values (abvrMonthName, MonthAvg), (abvrMonthNameCount, MonthCount)
) c (col, value)
请参见带演示的SQL小提琴.这将占用您的多列并将数据放入类似于以下格式的文件中:
See SQL Fiddle with Demo. This takes your multiple columns and places the data into a format similar to this:
| PRODUCTTITLE | COL | VALUE |
-------------------------------
| Product 1 | Dec | 0 |
| Product 1 | Dec# | 0 |
| Product 1 | Nov | 0 |
| Product 1 | Nov# | 0 |
| Product 1 | Oct | 0 |
| Product 1 | Oct# | 0 |
| Product 1 | Sep | 0 |
| Product 1 | Sep# | 0 |
一旦数据采用这种格式，您就可以将PIVOT应用于col中包含月份名称的值:
Once the data is in this format you can apply the PIVOT to the values in col which contains the month names:
select producttitle, jan, [jan#], feb, [feb#], mar, [mar#], apr, [apr#],
may, [may#], jun, [jun#], jul, [jul#], aug, [aug#],
sep, [sep#], oct, [oct#], nov, [nov#], dec, [dec#]
from
(
select t.producttitle, c.col, c.value
from tmpData t
cross apply
(
values (abvrMonthName, MonthAvg), (abvrMonthNameCount, MonthCount)
) c (col, value)
) d
pivot
(
sum(value)
for col in (jan, [jan#], feb, [feb#], mar, [mar#], apr, [apr#],
may, [may#], jun, [jun#], jul, [jul#], aug, [aug#],
sep, [sep#], oct, [oct#], nov, [nov#], dec, [dec#])
) piv;
请参见带演示的SQL提琴.得到的结果是:
See SQL Fiddle with Demo. This gives a result:
| PRODUCTTITLE | JAN | JAN# | FEB | FEB# | MAR | MAR# | APR | APR# | MAY | MAY# | JUN | JUN# | JUL | JUL# | AUG | AUG# | SEP | SEP# | OCT | OCT# | NOV | NOV# | DEC | DEC# |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Product 1 | 5 | 2 | 5 | 1 | 5 | 4 | 5 | 6 | 4.44 | 9 | 5 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Product 2 | 0 | 0 | 0 | 0 | 0 | 0 | 4.33 | 3 | 4.67 | 3 | 5 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Product 3 | 4.6 | 5 | 4.75 | 8 | 4.75 | 8 | 4 | 6 | 5 | 6 | 5 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
这篇关于SQL-枢转多个列而没有聚集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
