?
Solved

SQL Multi Pivot

Posted on 2014-10-09
6
Medium Priority
?
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40371995
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
ID: 40372005
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:Scott Pletcher
ID: 40372009
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
ID: 40372013
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 25

Expert Comment

by:chaau
ID: 40372020
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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