[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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

0
jamesjcooper
Asked:
jamesjcooper
1 Solution
 
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now