I cannot get the pivot section to work. can anyone assist.

Question

    select  case Mfr
          when 'Simmons' then 1
          else case when Manufacturer is null then 3
                    else 2
               end
        end as Expr1 ,
        case when Manufacturer <> '' then Manufacturer
             else 'Other'
        end as mymfr
from    tblMain
        left join tblUpdate on year(tblMain.AdDate) = tblUpdate.[Year]
        left join tblBrand on tblMain.mfr = tblBrand.Manufacturer
                              and tblmain.media not like '%tv%' pivot  

    ( month(addate) for addate in ( [1], [2], [3], [4], [5], [6], [7], [8],
                                    [9], [10], [11], [12] ) ) AS PVT
group by case Mfr
           when 'Simmons' then 1
           else case when Manufacturer is null then 3
                     else 2
                end
         end ,
        case when Manufacturer <> '' then Manufacturer
             else 'Other'
        end
order by case Mfr
           when 'Simmons' then 1
           else case when Manufacturer is null then 3
                     else 2
                end
         end

Answer 1

OK, you've got this a bit arse-about-face, as they say.

What you want to do is something more like this, logically speaking:

SELECT Unpivoted Fields, pivot values
FROM
(SELECT data with pivot values in columns) p
PIVOT
( aggregate function (field) FOR PivotValue IN (pivot values)
) as pvt
ORDER BY...

In other words, the second select is the one we built up in your previous query.

So, something more like:

SELECT Expr, MyMfr, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
FROM
(
    SELECT Case Mfr WHEN 'Simmons' THEN 1 ELSE Case WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END As Expr1,
        CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END AS MyMfr,
        MONTH(AdDate) AS AdDateMonth
    FROM tblMain LEFT JOIN tblUpdate ON YEAR(tblMain.AdDate) = tblUpdate.[Year]
                 LEFT JOIN tblBrand ON tblMain.Mfr = tblBrand.Manufacturer
                                       AND tblMain.Media NOT LIKE '%tv%'
    GROUP BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END,
             CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END
) p
PIVOT
(   COUNT(1) -- Assuming you're just counting the records
    FOR AdDateMonth IN ([1], [2], [3], [4], [5], [6[, [7], [8], [9], [10], [11], [12])
 ) AS pvt
ORDER BY MyMfr

Something like that.

Answer 2

MAn i'm so close.

Here is the access code

TRANSFORM Sum(tblMain.AdCost) AS SumOfAdSize
SELECT IIf(LCase([mfr])='simmons',1,IIf([manufacturer] Is Null,3,2)) AS Expr1, IIf([manufacturer]&lt;&gt;'',[manufacturer],'Other') AS mymfr
FROM tblUpdateTV, tblMain INNER JOIN tblBrand ON tblMain.Mfr = tblBrand.Manufacturer
WHERE (((Year([AdDate]))=[tblupdateTV].[year]) AND ((LCase([tblmain].[media])) Like '*tv*'))
GROUP BY IIf(LCase([mfr])='simmons',1,IIf([manufacturer] Is Null,3,2)), IIf([manufacturer]&lt;&gt;'',[manufacturer],'Other')
ORDER BY IIf(LCase([mfr])='simmons',1,IIf([manufacturer] Is Null,3,2))
PIVOT Month([addate]) In (1,2,3,4,5,6,7,8,9,10,11,12);

This is what prints to screen:

Expr1 mymfr 1 2 3 4 5 6 7 8 9 10 11 12
1 Simmons $3,551,813.70 $5,446,831.90 $2,549,293.30 $1,808,854.20 $3,727,596.20 $2,999,327.70 $2,605,302.00 $3,646,421.60 $2,749,367.00 $2,946,976.10 
2 Other $3,307,856.40 $4,318,058.70 $4,819,243.90 $4,028,371.50 $4,151,594.10 $4,252,828.30 $5,028,362.00 $5,233,628.10 $3,520,218.20 $3,792,489.60

Sql

SELECT Expr, MyMfr, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
FROM
(
    SELECT Case Mfr WHEN 'Simmons' THEN 1 ELSE Case WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END As Expr1,
        CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END AS MyMfr,
        MONTH(AdDate) AS AdDateMonth
    FROM tblMain LEFT JOIN tblUpdate ON YEAR(tblMain.AdDate) = tblUpdate.[Year]
                 LEFT JOIN tblBrand ON tblMain.Mfr = tblBrand.Manufacturer
                                       AND tblMain.Media NOT LIKE '%tv%'
    GROUP BY CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END,
             CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END
)p
PIVOT
(  sum(tblmain.AdCost) FOR month(AdDate) IN ([1], [2], [3], [4], [5], [6[, [7], [8], [9], [10], [11], [12]))
 ) AS pvt
ORDER BY MyMfr

error Msg 102, Level 15, State 1, Line 16 Incorrect syntax near '('.

Answer 3

Had to add few things but i thought you might like to see end result.

Thank you again.

SELECT Expr1, MyMfr, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] FROM ( SELECT Case Mfr WHEN 'Simmons' THEN 1 ELSE Case WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END As Expr1, CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END AS MyMfr, MONTH(AdDate) AS AdDateMonth, tblmain.AdCost FROM tblMain LEFT JOIN tblUpdate ON YEAR(tblMain.AdDate) = tblUpdate.[Year] LEFT JOIN tblBrand ON tblMain.Mfr = tblBrand.Manufacturer AND tblMain.Media NOT LIKE '%tv%' GROUP BY tblmain.AdDate, tblmain.AdCost, CASE Mfr WHEN 'Simmons' THEN 1 ELSE CASE WHEN Manufacturer IS NULL THEN 3 ELSE 2 END END, CASE WHEN Manufacturer <> '' THEN Manufacturer ELSE 'Other' END )P
PIVOT ( sum(AdCost) FOR addatemonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) ) AS pvt ORDER BY MyMfr

I cannot get the pivot section to work. can anyone assist.

Follow this question

Related Questions