How to join/flatten these tables

I have a table like this:

Table name: PRODUCT

ID     PRODUCTID     PRODUCTNAME
1      D100          Sample Product
2      K500          Another sample product

There is a related table that looks like 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 is that a product id always has the same sizes, just different colors. So if a D100 red comes in S, M and L, all D100s only come in S, M and L. A product can have up to 20 colors.

I need to write a sql statement that will output the 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           Another sample  Green      Red                           L   XL

The "COLOR1", "COLOR2" columns need to go to either "COLOR20" max or they need to just end at the max number of colors (i.e. if in the query, the biggest number of colors a product has is 4, it's fine to stop at COLOR4).

This is not an ideal way to do this, but it's the report that's been requested. Can anyone help me write a SQL statement that will accomplish this?

Thanks!

If you´re using 2005/2008 version of sql server you can use PIVOT. In order to stop at the maxium number of colors you can build this query dynamically. Here´s some help:

;WITH ProductColor as(
select  a.ProductId,
        b.ProductName,
        a.Color,
        RANK() over (partition by ProductName order by a.Id) r
from    dbo.COLORSIZE a left join
        dbo.PRODUCT b on a.PRODUCTID=b.PRODUCTID
)
SELECT ProductId,ProductName,[1], [2], [3]
FROM 
(select ProductId,ProductName,Color,r
from    ProductColor) p
PIVOT
(
MIN (Color)
FOR r IN
( [1], [2], [3] )
) AS pvt;

This will Start you off..

Your going to need to do SubQuerys but the problem is that your trying to make a table larger depending on the number of colours you have.

Your schema doesnt not work too well.

SELECT 
   A.ID
  ,B.PRODUCTID
  ,B.PRODUCTNAME
  ,B.COLOR1
  ,B.COLOR2
  ,B.COLOR3
  ,B.S
  ,B.M
  ,B.L
FROM 
     PRODUCT A
     INNER JOIN 
        COLORSIZE B 
               ON A.ProductID = B.ProductID