SQL Pivot Command

I am looking for some help on designing a simple pivot so that I can link it into other parts of my queries.

My data is like this

Items Table

Below is my table if I run Select * from items

ITEM      Weight    
12345         10    
12345         11    
654321        50    
654开发者_开发问答321        20    
654321       100

There are hundreds of Items in this table but each item code will only ever have maximum of 3 weight records each.

I want the desired output

ITEM     Weight_1    Weight_2     Weight_3
12345          10          11         null
654321         50          20          100

Would appreciate any suggestions, I have played around with pivots but each subsequent item puts the weights into weight 4,5,6,7,etc instead of starting at weight1 for each item.

Thanks

Update

Below is what I have used so far,

SELECT r.*
FROM   (SELECT 'weight' + CAST(Row_number() OVER (ORDER BY regtime ASC)AS
                               VARCHAR(10))
                      line,
               id,
               weight
        FROM   items it) AS o PIVOT(MIN([weight]) FOR line IN (weight1, weight2,
       weight3)) AS r  

You were almost there! You were only missing the PARTITION BY clause in OVER:

SELECT r.*
FROM   (SELECT 'weight' + CAST(Row_number() OVER (PARTITION BY id ORDER BY
                               regtime ASC)
                                      AS
                                              VARCHAR(10)) line,
               id,
               weight
        FROM   items it) AS o PIVOT(MIN([weight]) FOR line IN (weight1, weight2,
       weight3)) AS r  

When you PARTITION BY by ID, the row numbers are reset for each different ID.

Update

You do not need dynamic pivot, since you will always have 3 weights. But, if you ever need dynamic number of columns, take a look at some of the examples here:

• SQL Server PIVOT perhaps?
• Pivot data in T-SQL
• How do I build a summary by joining to a single table with SQL Server?

You will need a value to form the columns which I do with row_number. The outcome is what you want. The only negative that I have against PIVOT is that you need to know how many columns in advance. I use a similar method, but build up the select as dynamic SQL and can then insert my columns.

EDIT: updated to show columns as weight1, weight2, etc.

create table #temp (Item int, Weight int)

insert into #temp (Item, Weight) 
Values (12345, 10),
       (12345, 11),
       (654321, 50),
       (654321, 20),
       (654321, 200)

SELECT *
  FROM (SELECT Item,
               Weight,
               'weight' + cast(Row_number() 
                          OVER (partition by Item order by item) as varchar(10)) as seq
          FROM #temp) as Src 
        PIVOT (  MAX(Weight) FOR Seq IN ([Weight1], [Weight2], [Weight3])  ) as PVT

MySQL
Whenever you need a pivot, use group_concat it will output a CSV list of the values you need.
Once you get used to working with it, it's a great tool.

SELECT item, GROUP_CONCAT(weight) as weights FROM table1
GROUP BY item

See: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

TSQL aka SQL-server
Many many questions on this because T-SQL supports a pivot keyword.

See:
Transact SQL Query-Pivot-SQL
Pivot data in T-SQL