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 '12 at 07:27 PM in Default

artistlover gravatar image

artistlover
576 9 31 46

(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 '12 at 10:22 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

Note that I wrote that late at night and without testing anything... ;-)
Dec 12 '12 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 '12 at 04:02 PM

artistlover gravatar image

artistlover
576 9 31 46

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 '12 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 '12 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 '12 at 04:51 PM

artistlover gravatar image

artistlover
576 9 31 46

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x76

asked: Dec 11 '12 at 07:27 PM

Seen: 577 times

Last Updated: Dec 12 '12 at 04:51 PM