sql - Single Column as N Rows -

this question has answer here:

• efficiently convert rows columns in sql server 3 answers

i have large table 3 columns follows:

invoice product     color 1       pant        red 1       pant        black 1       shirt       green 2       pant        white 2       pant        black 2       pant        blue 

i'd group on invoice & product , have unique color values appear on related grouped record follows:

invoice product     colour1     colour2     colour3 1       pant        red         black 1       shirt       green 2       pant        white       black       blue 

is possible in sql server?

it possible in sql server -- if know there 3 color columns. if there variable number, still possible, requires dynamic sql.

i approach using conditional aggregation:

select invoice, product,        max(case when seqnum = 1 colour end) colour1,        max(case when seqnum = 2 colour end) colour2,        max(case when seqnum = 3 colour end) colour3 (select t.*,              row_number() on (partition invoice, product order (select null)) seqnum       table t      ) t group invoice, product;