Solved

SQL Multi Pivot

Posted on 2014-10-09
6
236 Views
Last Modified: 2014-10-10
Good afternoon all,

I have a table that I am trying to add an additional column to a pivot.   I have two tables, one with personnel records, and another with credentials, of which a person can have up to three.   I have a query with a pivot table working to return the personnel information and their card number, but I also need to get the activation and expiration date for each of the respective cards.   My goal is to have a single row with ID, name, card1 value, card1 activation, card1 expiration, card2 value, card2 activation, card2 expiration, card3 value, card3 activation, card3 expiration

I have the three columns pivoting, but they are not being grouped by the person ID.   I get one row with all the personnel info, and the card1 info, another row with the personnel info and the card2 info.   I tried to add a simple group by clause at the end, but get a message that the multi-part identifier can not be bound.

Any suggestions?   Code is below

SELECT TOP (100) PERCENT *
FROM (SELECT     
personnel.objectID as ObjectID, 
personnel.firstname, 
personnel.lastname, 
Stuff('E600000000000000', 17 - Len(UPPER(dbo.converttobase(credential.cardnumber, 16))), Len(UPPER(dbo.converttobase(credential.cardnumber, 16))), UPPER(dbo.converttobase(credential.cardnumber, 16))) AS CardNumber, 
credential.activationdatetime as ActivationDateTime, 
credential.expirationdatetime As ExpirationDateTime, 
'CARD' + CONVERT(varchar, ROW_NUMBER() OVER (PARTITION BY credential.personnelID ORDER BY credential.personnelid)) AS CardRow,
'ACT' + CONVERT(varchar, ROW_NUMBER() OVER (PARTITION BY credential.personnelID ORDER BY credential.personnelid)) AS ActRow,
'EXP' + CONVERT(varchar, ROW_NUMBER() OVER (PARTITION BY credential.personnelID ORDER BY credential.personnelid)) AS ExpRow, 
personnel.disabled AS disabled
FROM personnel 
JOIN credential ON credential.personnelID = personnel.objectID

WHERE credential.active = '1' AND credential.lost = '0' AND credential.stolen = '0' AND credential.disabled = '0') x
PIVOT (max(cardnumber) FOR Cardrow IN ([CARD1], [CARD2], [CARD3])) as p
pivot (max(ActivationDateTime) for Actrow in ([ACT1],[ACT2],[ACT3])) as q 
pivot (max(ExpirationDateTime) for Exprow in ([EXP1],[EXP2],[Exp3])) as r

Open in new window

0
Comment
Question by:jamesjcooper
6 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
You can use GROUP BY in this case. However, you will have to define the aggregate functions for the pivoted columns:
SELECT ObjectID, firstname, lastname,
SUM([CARD1]) [CARD1],
SUM([CARD2]) [CARD2], 
SUM([CARD3]) [CARD3],
SUM([ACT1]) [ACT1],
SUM([ACT2]) [ACT2], 
SUM([ACT3]) [ACT3],
SUM([EXP1]) [EXP1],
SUM([EXP2]) [EXP2], 
SUM([EXP3]) [EXP3]
FROM (SELECT    
personnel.objectID as ObjectID,
personnel.firstname,
personnel.lastname,
Stuff('E600000000000000', 17 - Len(UPPER(dbo.converttobase(credential.cardnumber, 16))), Len(UPPER(dbo.converttobase(credential.cardnumber, 16))), UPPER(dbo.converttobase(credential.cardnumber, 16))) AS CardNumber,
credential.activationdatetime as ActivationDateTime,
credential.expirationdatetime As ExpirationDateTime,
'CARD' + CONVERT(varchar, ROW_NUMBER() OVER (PARTITION BY credential.personnelID ORDER BY credential.personnelid)) AS CardRow,
'ACT' + CONVERT(varchar, ROW_NUMBER() OVER (PARTITION BY credential.personnelID ORDER BY credential.personnelid)) AS ActRow,
'EXP' + CONVERT(varchar, ROW_NUMBER() OVER (PARTITION BY credential.personnelID ORDER BY credential.personnelid)) AS ExpRow,
personnel.disabled AS disabled
FROM personnel
JOIN credential ON credential.personnelID = personnel.objectID
WHERE credential.active = '1' AND credential.lost = '0' AND credential.stolen = '0' AND credential.disabled = '0') x
PIVOT (max(cardnumber) FOR Cardrow IN ([CARD1], [CARD2], [CARD3])) as p
pivot (max(ActivationDateTime) for Actrow in ([ACT1],[ACT2],[ACT3])) as q
pivot (max(ExpirationDateTime) for Exprow in ([EXP1],[EXP2],[Exp3])) as r ) S
GROUP BY ObjectID, firstname, lastname

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
My goal is to have a single row with ID, name, card1 value, card1 activation, card1 expiration, card2 value, card2 activation, card2 expiration, card3 value, card3 activation, card3 expiration
Perhaps I am being dense, but this would be an UNPIVOT, not a PIVOT.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I'd do it something like below instead.  I use subqueries rather than CTEs because it shows the structure of query dependencies more directly, but the subqueries could probably be converted to CTEs also.


 SELECT TOP (100) PERCENT *
 FROM (SELECT     
         p.objectID as ObjectID, 
         p.firstname, 
         p.lastname, 
         c.Card1_Value,
         c.Card1_Activation,
         c.Card1_Expiration,
         c.Card2_Value,
         c.Card2_Activation,
         c.Card2_Expiration,
         c.Card3_Value,
         c.Card3_Activation,
         Card3_Expiration,
         p.disabled AS disabled
     FROM personnel p
     INNER JOIN (
         SELECT personnelID, 
             MAX(CASE WHEN CardRow = 1 THEN CardNumber END) AS Card1_Value,
             MAX(CASE WHEN CardRow = 1 THEN ActivationDateTime END) AS Card1_Activation,
             MAX(CASE WHEN CardRow = 1 THEN ExpirationDateTime END) AS Card1_Expiration,
             MAX(CASE WHEN CardRow = 2 THEN CardNumber END) AS Card2_Value,
             MAX(CASE WHEN CardRow = 2 THEN ActivationDateTime END) AS Card2_Activation,
             MAX(CASE WHEN CardRow = 2 THEN ExpirationDateTime END) AS Card2_Expiration,
             MAX(CASE WHEN CardRow = 3 THEN CardNumber END) AS Card3_Value,
             MAX(CASE WHEN CardRow = 3 THEN ActivationDateTime END) AS Card3_Activation,
             MAX(CASE WHEN CardRow = 3 THEN ExpirationDateTime END) AS Card3_Expiration
         FROM (
             SELECT personnelID, CardNumber, ActivationDateTime, ExpirationDateTime,
                 ROW_NUMBER() OVER (PARTITION BY personnelID ORDER BY ActivationDateTime) AS CardRow
             FROM credential
             CROSS APPLY (
                 SELECT UPPER(dbo.converttobase(cardnumber, 16)) AS base_cardnumber
             ) AS ca1 --assign alias names to calc(s)
             CROSS APPLY (
                 SELECT Stuff('E600000000000000', 17 - Len(ca1.base_cardnumber), 
                     Len(ca1.base_cardnumber), ca1.base_cardnumber) AS CardNumber
             ) AS ca2 --assign alias names to calc(s)
             WHERE active = '1' AND lost = '0' AND stolen = '0' AND disabled = '0'
        ) AS c2
        GROUP BY personnelID
     ) AS c ON c.personnelID = p.objectID
 ) AS x

Open in new window

0
 
LVL 1

Author Closing Comment

by:jamesjcooper
Comment Utility
That worked great!   I just had to change the SUM to MAX because there are some non-numeric characters in there, the value is in HEX.

If I understand correctly, I can use group by if I specify the columns from the sub queries as opposed to using a wildcard?
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Yes. Any SELECT query can be enclosed to brackets and assigned an alias. After you put a query into a sub-query the columns must be referred to as if the sub-query is a "view". You cannot use the aliases from the sub-query in the outside query.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now