Solved

SQL Multi Pivot

Posted on 2014-10-09
6
281 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 500 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

729 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