pivot rows to 14 columns as 7 tuples

Question

I have a table looks like this

assignmentID    personID    projectCode projectCodePercent
-------------- ------------ ----------- ----------- 
642             13527       511         75.00
642             13527       621         25.00
650             12000       555         50.00
650             12000       520         25.00
650             12000       621         25.00
240             56000       721         100.00

I want to know the distribution of (porjectCode , projectCodePercent) as tuple for each assignment. An assignment can have up to 7 tuples. if they don't have 7 then those field can just be blank. So the desired output should look below:

assignmentID    projectCode1  projectCodePercent1   projectCode2   projectCodePercent2   projectCode3   projectCodePercent3   projectCode4   projectCodePercent4   projectCode5   projectCodePercent5   projectCode6   projectCodePercent6   projectCode7   projectCodePercent7
------------    ------------  -------------------   ------------   -------------------   -------------   ------------------- -------------   -------------------   -------------  -------------------   -------------   -------------------  ------------   ----------------
 642            511           75.00                 621            25.00
 650            555           50.00                 520            25.00                 621             25.00
 240            721           100.00

EDIT: the record order doesn't matter. ie. which record gets assign to projectCode1 or projectCode2....and so on does not matter as long as the right projectCode matches the correct projectCodePercent

Answer 1

DEMO: http://rextester.com/IYDJ29385

1. CTE gives me sample data to play with
2. CTE2 simply assigns a row number to each assignmentId and PersonId ( you could just use a an inline view)
3. Then we use a case statement to pivot the data based on the row numbers generated. The downfall to this approach is all 14 columns are always returned. Using Dynamic SQL you could only show those columns when needed.

This does assume that a assignmentId and personId and projectcode are unique. If multiple project codes could exist for the same assignment and person then we would need to do something different instead of max.

WITH CTE (assignmentID,    personID,    projectCode, projectCodePercent) as  (
SELECT 642,             13527,       511,         75.00 UNION ALL
SELECT 642,             13527,       621,         25.00 UNION ALL
SELECT 650,             12000,       555,         50.00 UNION ALL
SELECT 650,             12000,       520,         25.00 UNION ALL
SELECT 650,             12000,       621,         25.00 UNION ALL
SELECT 240,             56000,       721,         100.00),
cte2 as (SELECT A.*, row_number() over (partition by AssignmentID, PersonID order by projectCode) RN 
      FROM cte A)
SELECT AssignmentID
     , PersonID
     , max(CASE WHEN RN = 1 then projectCode end) as projectCode1
     , max(CASE WHEN RN = 1 then ProjectcodePercent end) as ProjectcodePercent1
     , max(CASE WHEN RN = 2 then projectCode end) as projectCode2
     , max(CASE WHEN RN = 2 then ProjectcodePercent end) as ProjectcodePercent2
     , max(CASE WHEN RN = 3 then projectCode end) as projectCode3
     , max(CASE WHEN RN = 3 then ProjectcodePercent end) as ProjectcodePercent3
     , max(CASE WHEN RN = 4 then projectCode end) as projectCode4
     , max(CASE WHEN RN = 4 then ProjectcodePercent end) as ProjectcodePercent4
     , max(CASE WHEN RN = 5 then projectCode end) as projectCode5
     , max(CASE WHEN RN = 5 then ProjectcodePercent end) as ProjectcodePercent5
     , max(CASE WHEN RN = 6 then projectCode end) as projectCode6
     , max(CASE WHEN RN = 6 then ProjectcodePercent end) as ProjectcodePercent6
     , max(CASE WHEN RN = 7 then projectCode end) as projectCode7
     , max(CASE WHEN RN = 7 then ProjectcodePercent end) as ProjectcodePercent7
FROM CTE2
Group by AssignmentID, personId

Giving us:

+----+--------------+----------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+
|    | AssignmentID | PersonID | projectCode1 | ProjectcodePercent1 | projectCode2 | ProjectcodePercent2 | projectCode3 | ProjectcodePercent3 | projectCode4 | ProjectcodePercent4 | projectCode5 | ProjectcodePercent5 | projectCode6 | ProjectcodePercent6 | projectCode7 | ProjectcodePercent7 |
+----+--------------+----------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+
|  1 |          650 |    12000 |          520 |               25,00 | 555          | 50,00               | 621          | 25,00               | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                |
|  2 |          642 |    13527 |          511 |               75,00 | 621          | 25,00               | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                |
|  3 |          240 |    56000 |          721 |              100,00 | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                | NULL         | NULL                |
+----+--------------+----------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+--------------+---------------------+

Answer 2

Since you don't need to go dynamic. One approach is to UNPivot the data via a CROSS APPLY.

Example

Select *
 From (
        Select assignmentID    
              ,B.* 
            From (
                  Select *,Grp = Row_Number() over (Partition By assignmentID  order by projectCode )
                   From  YourTable
                 ) A
            Cross Apply (values ('projectCode'       +left(A.Grp,1),cast(projectCode as varchar(max)))
                               ,('projectCodePercent'+left(A.Grp,1),cast(projectCodePercent as varchar(max)))
                               ,('projectCode'       +left(A.Grp,1),cast(projectCode as varchar(max)))
                        ) B(Item,Value)
      ) A
 Pivot (max([Value]) For [Item] in (projectCode1,projectCodePercent1,projectCode2,projectCodePercent2,projectCode3,projectCodePercent3,projectCode4,projectCodePercent4,projectCode5,projectCodePercent5,projectCode6,projectCodePercent6,projectCode7,projectCodePercent7) ) p

Returns