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
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?
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.
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.
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)
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 |
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;
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表，用于计算另一个SQL表中的值