How to use UNPIVOT with inner joined tables

Hi,
I previously asked a question "T-SQL How to sum multiple columns and get the totals by the column name?" The answer was to use UNPIVOT this worked swimmingly where I was using only one table, in practice however I need to use two tables as I need location data to restrict the number of records to a particular customer and location.  So flowing out of that question I have this one:

Just one further question on UNPIVOT, This is the actual T-SQL that I got working from an example given by dsacker:

SELECT ActionType,
        SUM(TotalAmount) AS TotalValue
FROM IncidentReport
UNPIVOT (Include For ActionType IN (ArrestYN
      ,DeterYN
      ,TheftYN
      ,ViolentBehaviourYN
      ,AbusiveBehaviourYN
      ,BanFromStoreYN
      ,CriminalDamageYN
      ,UnderagePurchaseYN
      ,CreditCardFraudYN
      ,AntiSocialBehaviourYN
      ,SuspiciousBehaviourYN
      ,OtherYN)) as unpvt
WHERE (IncidentDate BETWEEN CONVERT(smalldatetime, CONVERT(nvarchar(4),YEAR(getDate())) + '-01-01 00:00:01',103) AND GetDate()) AND RecordIsDeletedYN=0 AND Include=1
GROUP BY ActionType

This produces this result:
ActionType                          TotalValue
AbusiveBehaviourYN                  44.97
AntiSocialBehaviourYN           29.99
ArrestYN                               1520.24
BanFromStoreYN                        279.77
CreditCardFraudYN                  32.40
CriminalDamageYN                    0.00
DeterYN                                     1263.24
OtherYN                                         19.98
SuspiciousBehaviourYN      125.14
TheftYN                                    1695.45
UnderagePurchaseYN                32.40
ViolentBehaviourYN                93.48

Brilliant.

The trouble is I need to join another table called "Site" so that I can produce the above list for a given Region and Customer . I'd like to include the other table by using an inner join like this:

SELECT ActionType, SUM(TotalAmount) AS TotalValue
FROM IncidentReport inner join Site on IncidentReport.fkSiteID=Site.SiteID
UNPIVOT (Include For ActionType IN (ArrestYN,
            DeterYN,
            TheftYN,
            ViolentBehaviourYN,
            AbusiveBehaviourYN,
            BanFromStoreYN,
            CriminalDamageYN,
            UnderagePurchaseYN,
            CreditCardFraudYN,
            AntiSocialBehaviourYN,
            SuspiciousBehaviourYN,
            OtherYN)) as unpvt
WHERE IncidentReport.fkCustomerID=3
        AND IncidentReport.RecordIsDeletedYN=0
        AND (IncidentReport.IncidentDate BETWEEN CONVERT(smalldatetime, CONVERT(nvarchar(4),YEAR(getDate())) + '-01-01 00:00:01',103) AND GetDate())
        AND Include=1
GROUP BY ActionType

This gives me this error when I try and construct it like the above:

Msg 8156, Level 16, State 1, Line 14
The column 'fkCustomerID' was specified multiple times for 'unpvt'.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "IncidentReport.fkCustomerID" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "IncidentReport.RecordIsDeletedYN" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "IncidentReport.IncidentDate" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "IncidentReport.IncidentDate" could not be bound.

Is there any way I can do this?
SivAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Use sub-query or CTE to provide the data. E.g.

WITH Data AS 
  (
    yourQueryWithTheJoinedTable
  )
  SELECT *
  FROM Data D
  UNPIVOT ( .. ) U;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SivAuthor Commented:
ste5an,
Thanks for this, will try to convert what I have to this format.
Siv
0
SivAuthor Commented:
ste5an,

OK after much furtling I get to this:

WITH Data AS
(
      SELECT ArrestYN AS A,
            DeterYN AS B,
            TheftYN AS C,
            ViolentBehaviourYN AS D,
            AbusiveBehaviourYN AS E,
            BanFromStoreYN AS F,
            CriminalDamageYN AS G,
            UnderagePurchaseYN AS H,
            CreditCardFraudYN AS J,
            AntiSocialBehaviourYN AS K,
            SuspiciousBehaviourYN AS L,
            OtherYN AS M,
            TotalAmount
      FROM IncidentReport inner join Site
      ON IncidentReport.fkSiteID=Site.SiteID
      WHERE IncidentReport.fkCustomerID=3
            AND IncidentReport.RecordIsDeletedYN=0
            AND (IncidentReport.IncidentDate BETWEEN CONVERT(smalldatetime, CONVERT(nvarchar(4),YEAR(getDate())) + '-01-01 00:00:01',103) AND GetDate())
)
SELECT ActionType, SUM(TotalAmount) AS TotalValue
FROM Data D
UNPIVOT (Include For ActionType IN (A,B,C,D,E,F,G,H,J,K,L,M)) as U
WHERE Include=1
GROUP BY ActionType

Which gives me the results:

ActionType      TotalValue
A      88.95
B      272.94
C      1152.32
D      0.00
E      0.00
F      0.00
G      0.00
K      0.00
L      0.00
M      0.00

Blinding! :)
Thanks for your and dsacker in the previous question who have helped me to a result that works!!

Siv
0
SivAuthor Commented:
Thanks for your help this has done the trick and I can move forward again!
Siv
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.