在没有Aggregrates的情况下支持多列
我不确定如何将数据转接到特定的视图。 以下是测试数据。
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没有分组。
我将如何实现这一目标，还是完全向错误的方向发展？
问题的一部分是您已经对要转换的多个列进行了非标准化数据。 理想情况下，您应该考虑修复表结构，以便维护和查询更容易。 如果您无法修复表格结构，那么您应该先取消转印列，然后应用PIVOT以获得最终结果。
UNPIVOT进程将采用多个列并将它们转换为多行。 根据您的SQL Server版本，您可以通过几种方法来执行此操作。 您可以使用UNPIVOT函数，或者因为您使用的是SQL Server 2008，所以可以使用带有VALUES子句的CROSS APPLY来反转。
CROSS APPLY / VALUES代码将为：
select t.producttitle, c.col, c.value
from tmpData t
cross apply
(
values (abvrMonthName, MonthAvg), (abvrMonthNameCount, MonthCount)
) c (col, value)
使用Demo查看SQL小提琴。 这需要你的多个列，并将数据放入类似这样的格式：
| 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中包含月份名称的值：
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;
使用Demo查看SQL小提琴。 这给出了一个结果：
| 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 |
上一篇: Pivot multiple columns without Aggregrates
下一篇: Traversing bytes of a 500 MB file hosted remotely on a server in PHP