Pivot Data Using a SQL Select Query

Question

I need to pivot some data when doing a select query. I'm using SQL Server 2014. Here is the format of the original data.

    StudentID  | DocumentType  | PersonID
    ----------   -------------   --------
    00001           DocA           2222
    00001           DocB           2222
    00002           DocB           2222
    00002           DocA           3333
    00003           DocA           4444

And I want it to display like...

    StudentID |  DocumentTypeAPersonID | DocumentTypeBPersonID
    ---------    ---------------------   -----------------------
    00001               2222                     2222
    00002               3333                     2222
    00003               4444                     NULL

Sometimes a student will have both document types. Sometimes they will only have one. Not sure if the "missing" document type would show up as NULL or just blank in that field.

Thanks. It's SQL Server 2014. I'm using Microsoft SQL Server Management Studio to run my lookup queries. — bosstone75, Sep 23 '16 at 16:13

score 2 · Accepted Answer · answered Sep 23 '16 at 18:12

this way might save you some code

SELECT  StudentID,
        DocumentTypeAPersonID = MAX(CASE WHEN DocumentType ='DocA' THEN PersonID END),
        DocumentTypeBPersonID = MAX(CASE WHEN DocumentType ='DocB' THEN PersonID END)
FROM    MyTable
GROUP BY StudentID

score 0 · Answer 2 · answered Sep 23 '16 at 18:02

Here you go.

SELECT StudentID, DocA, DocB FROM 
(
    SELECT StudentID, DocumentType, PersonID
    FROM myTable
) t
pivot
(
    MAX(PersonID)
    FOR DocumentType IN (DocA, DocB)
) p

This is a static pivot meaning that you have to manually input the columns you want to pivot. If, for example, you also have a DocC then just do this...

SELECT StudentID, DocA, DocB, DocC FROM 
(
    SELECT StudentID, DocumentType, PersonID
    FROM myTable
) t
pivot
(
    MAX(PersonID)
    FOR DocumentType IN (DocA, DocB, DocC)
) p

Pivot Data Using a SQL Select Query

2 Answers2