Return values from multiple rows as columns with group by on multiple colum

Question

Is it possible to return multiple row values into colum by group by on multiple column

with distinct.

If my table is:

QUantityTypeID Quantity OrderDate CompanyID TypeName

-------------- -------- ----------- ---------------- ----------

1 7.0000 2013-09-02 1 Sales

1 4.0000 2013-09-15 1 Sales

1 1.0000 2013-09-16 1 Sales

1 1.0000 2013-09-16 2 Sales

2 1.0000 2013-08-25 1 Sales

3 1.0000 2013-08-25 1 Sales

4 1.0000 2013-09-11 1 Sales

The select should return:

OrderDate 1 2 3 4 CompanyID TypeName

----------- ----- ----- ------ ------ ---------------- ----------

2013-08-25 NULL 1.0000 1.0000 NULL 1 Sales

2013-09-02 7.0000 NULL NULL NULL 1 Sales

2013-09-11 NULL NULL NULL 1.0000 1 Sales

2013-09-15 4.0000 NULL NULL NULL 1 Sales

2013-09-16 1.0000 NULL NULL NULL 1 Sales

2013-09-16 1.0000 NULL NULL NULL 2 Sales

See http://stackoverflow.com/questions/24470/sql-server-pivot-examples/40434#40434 — bfavaretto, Sep 21 '13 at 22:12

score 2 · Accepted Answer · answered Sep 21 '13 at 22:13

2

You need to pivot your data.

SELECT
  OrderDate, [1], [2], [3], [4], CompanyID, TypeName 
FROM test
PIVOT
(
  MAX(Quantity)
  FOR QUantityTypeID IN ([1], [2], [3], [4])
) as p

answered Sep 21 '13 at 22:13

Hamlet Hakobyan

1 Answers1