SQL Pivot Command

Question

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    
654321        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  

Answer 1

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?

Answer 2

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

Answer 3

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