sql server - How to join/flatten these tables -

i have table this:

table name: product

id     productid     productname 1      d100          sample product 2      k500          sample product 

there related table looks this:

table name: colorsize

id     productid     color          s       m      l     xl 1      d100          red            s       m      l  2      d100          black          s       m      l 3      d100          blue           s       m      l 4      k500          green                         l     xl 4      k500          red                           l     xl 

the general rule product id has same sizes, different colors. if d100 red comes in s, m , l, d100s come in s, m , l. product can have 20 colors.

i need write sql statement output following

productswithcolors

id     product id     productname     color1     color2     color3   s    m    l   xl 1      d100           sample product  red        black      blue     s    m    l 2      k500           sample  green      red                           l   xl 

the "color1", "color2" columns need go either "color20" max or need end @ max number of colors (i.e. if in query, biggest number of colors product has 4, it's fine stop @ color4).

this not ideal way this, it's report that's been requested. can me write sql statement accomplish this?

thanks!

if you´re using 2005/2008 version of sql server can use pivot. in order stop @ maxium number of colors can build query dynamically. here´s help:

;with productcolor as( select  a.productid,         b.productname,         a.color,         rank() on (partition productname order a.id) r    dbo.colorsize left join         dbo.product b on a.productid=b.productid ) select productid,productname,[1], [2], [3]  (select productid,productname,color,r    productcolor) p pivot ( min (color) r in ( [1], [2], [3] ) ) pvt;