CODEWITHSUNDEEP
sqlsql-serverpivot
Robert
Robert

Reputation: 53

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

Upvotes: 5

Views: 2101

Answers (3)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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:

Upvotes: 2

Leons
Leons

Reputation: 2674

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

Upvotes: 1

Johan
Johan

Reputation: 76670

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

Upvotes: 0

Related Questions