Selecting from a flat table

I've got a table with columns like this SubjectID UserID. There are many subjectIDs f开发者_Python百科or a single user. I need to do select somehow to get a result set similar to this:

UserID1 SubjectID1 SubjectID2 Subject3
UserID2 SubjectID1 SubjectID2 Subject3
UserID3 SubjectID1 SubjectID2 Subject3

Which Database are you using ? Microsoft SQL Server (2005 onwards) has PIVOT/UNPIVOT to transpose Rows to columns and vice versa. http://msdn.microsoft.com/en-us/library/ms177410.aspx

For purposes of discussion, let's define a sample dataset in a table called SubjectUser as follows:

SELECT NULL AS SubjectId, NULL AS UserID INTO SubjectUser WHERE 1=0
UNION SELECT 'cpsc500', 'maxt3r'
UNION SELECT 'phil507', 'zontar33'
UNION SELECT 'phil507', 'maxt3r'
UNION SELECT 'eng501',  'zontar33'
UNION SELECT 'eng501',  'maxt3r'
UNION SELECT 'bkwv101', 'spaced99'

You can then get the desired result using this query:

;WITH
  orderedSubjects AS (
    SELECT
      UserId
    , SubjectId
    , 'SubjectId'
      + CAST(
          ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SubjectId)
          AS NVARCHAR )
      AS col
    FROM SubjectUser
  )
SELECT *
FROM orderedSubjects
PIVOT (
  MAX(SubjectId)
  FOR col IN (SubjectId1, SubjectId2, SubjectId3)
) AS p

Let's look at this query in parts. The first problem is to assign column names to each user's subjects. The orderedSubjects table serves this purpose, defined by this query:

SELECT
  UserId
, SubjectId
, 'SubjectId'
  + CAST(
      ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SubjectId)
      AS NVARCHAR )
  AS col
FROM SubjectUser

ROW_NUMBER() ... PARTITION BY ... ORDER BY is used to assign a sequence number to each user's subjects. Arbitrarily, the subjects are ordered alphabetically. Then, a column name is generated by prefixing each sequence number with the string SubjectId. The result is as follows:

UserId     SubjectId   col
maxt3r     cpsc500     SubjectId1
maxt3r     eng501      SubjectId2
maxt3r     phil507     SubjectId3
spaced99   bkwv101     SubjectId1
zontar33   eng501      SubjectId1
zontar33   phil507     SubjectId2

We now have the data we need to create the final result. It is obtained by pivoting orderedSubjects:

SELECT *
FROM orderedSubjects
PIVOT (
  MAX(SubjectId)
  FOR col IN (SubjectId1, SubjectId2, SubjectId3)
) AS p

The application of the MAX is necessary only because the PIVOT syntax requires the use of an aggregation function. In the case at hand, there is only one SubjectID in each group, so MAX could just as easily have been MIN.

Also note that the SQL Server PIVOT syntax demands that the set of generated pivot columns be fixed in number. In this case, the query is hard-coded for three subjects. If the data contains more values than will fit into the allocated number of columns, the excess are discarded.

The final result is as follows:

UserId      SubjectId1      SubjectId2      SubjectId3
maxt3r      cpsc500         eng501          phil507
spaced99    bkwv101         NULL            NULL
zontar33    eng501          phil507         NULL