Flatten table rows by combining certain values into columns

Question

I'm trying to build a view for reporting purposes and am having trouble flattening results to display what would normally multiple rows into a single row, by combining a few values into columns. Each organization has two administrators, and would like to display them both in the same row. Query is as follows:

SELECT dbo.Organization.OrganizationName,
       CASE 
        WHEN (dbo.OrganizationUser.AdminOrder = 1) 
        THEN dbo.OrganizationUser.UserID 
        ELSE '' 
       END AS Admin1,
       CASE 
        WHEN (dbo.OrganizationUser.AdminOrder = 2) 
            THEN dbo.OrganizationUser.UserID 
        ELSE '' 
       END AS Admin2
FROM   dbo.Organization 
INNER JOIN dbo.OrganizationUser 
    ON dbo.OrganizationUser.OrganizationId = dbo.Organization.OrganizationId
WHERE  dbo.OrganizationUser.UserType = 'ADMIN'

Problem is, I get 2 rows that look like

Organization1 - User1   - <empty>
Organization1 - <empty> - User2

I cannot get the rows to consolidate.

Does your DB support a pivot function? It will allow you to transpose rows into columns. — user2793390, Mar 14 '14 at 16:16

score 0 · Accepted Answer · answered Mar 14 '14 at 18:49

This is a pretty standard operation; you're just missing the second half of it. You need a surrounding query that does the consolidation step:

select foo.orgname, max(foo.Admin1), max(foo.Admin2)
from   (SELECT dbo.Organization.OrganizationName as orgname,
               CASE WHEN (dbo.OrganizationUser.AdminOrder = 1) THEN dbo.OrganizationUser.UserID ELSE '' END AS Admin1,
               CASE WHEN (dbo.OrganizationUser.AdminOrder = 2) THEN dbo.OrganizationUser.UserID ELSE '' END AS Admin2
        FROM   dbo.Organization 
         INNER JOIN dbo.OrganizationUser ON dbo.OrganizationUser.OrganizationId = dbo.Organization.OrganizationId
        WHERE  dbo.OrganizationUser.UserType = 'ADMIN') foo
group  by foo.orgname

You're welcome. Note that instead of saying "thank you" on StackOverflow, we typically upvote and/or accept the responder's answer. — dg99, Mar 14 '14 at 22:27

Flatten table rows by combining certain values into columns

1 Answers1