SQL Multi Pivot

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

LVL 1
jamesjcooperAsked:
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.

chaauCommented:
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

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
Anthony PerkinsCommented:
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
Scott PletcherSenior DBACommented:
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
jamesjcooperAuthor Commented:
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
chaauCommented:
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
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.