x

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

     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
more ▼

asked Dec 11, 2012 at 07:27 PM in Default

avatar image

artistlover
806 47 63 67

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Dec 11, 2012 at 10:22 PM

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

Note that I wrote that late at night and without testing anything... ;-)

Dec 12, 2012 at 04:21 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

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 '('.

more ▼

answered Dec 12, 2012 at 04:02 PM

avatar image

artistlover
806 47 63 67

OK, looks like you've got an extra ) at the end of the PIVOT's IN clause.

Also, instead of using FOR month(AdDate), use the AdDateMonth field that was created in the inner SELECT statement.

For some examples and notes on the PIVOT statement, see the MS documentation at http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Dec 12, 2012 at 04:16 PM ThomasRushton ♦♦

Oh yes - you'll need to include the AdCost in the inner SELECT statement, and cchange the PIVOT's SUM to just SUM(AdCost)

Dec 12, 2012 at 04:22 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 12, 2012 at 04:51 PM

avatar image

artistlover
806 47 63 67

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x104

asked: Dec 11, 2012 at 07:27 PM

Seen: 794 times

Last Updated: Dec 12, 2012 at 04:51 PM

Copyright 2016 Redgate Software. Privacy Policy