1

I have a query that returns a dataset with 2 columns, as shown below:

--------------------------
|spec-number|project    |
|-----------|-----------|
|649841     |A          |
|649841     |B          |
|649841     |D          |
|84709      |E          |
|84709      |B          |
|84709      |C          |
|84709      |K          |
-------------------------

The number of projects a spec-number could be involved in could potentially be infinite.

What I would like the result to look like is:

-------------------------------------------------
|spec-number|project1|project2|project3|project4|
|-----------|--------|--------|--------|--------|  
|649841     |A       |B       |D       |        | 
|84709      |E       |B       |C       |K       |
-------------------------------------------------

I tried pivoting but it just gives me a cross tab of projects by spec-number. Any help would be greatly appreciated!

ılǝ
  • 3,440
  • 2
  • 33
  • 47
parakkrama perera
  • 13
  • 4

1 Answers1

0

If you want them as separate columns, then you can calculate the project number and use that for pivoting. The following uses the group by method for aggregation:

select spec_num,
       max(case when seqnum = 1 then project end) as project1,
       max(case when seqnum = 2 then project end) as project2,
       max(case when seqnum = 3 then project end) as project3,
       max(case when seqnum = 4 then project end) as project4
from (select t.*,
             row_number() over (partition by spec_number order by project) as seqnum
      from t
     ) t
group by spec_num;

If you need a variable number of columns, then you'll have to use dynamic SQL. This can also be phrased as a pivot query, if you prefer.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. Almost there! there could be a variable number of project columns. Could you point me to an example of the dynamic sql code you are talking about? – parakkrama perera Sep 11 '13 at 02:30
  • Here is an example: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query. – Gordon Linoff Sep 11 '13 at 02:32