make multiple rows one row with additional columns

Question

I have a table that looks like

Customer_id     Order_id   Action    Time_Stamp
abc             123         placed     8/1/2012 10:00AM
abc             123         processed  8/1/2012 10:30AM
abc             123         shipped    8/1/2012 11:00AM

xyz             456         recieved   8/1/12 12:00PM

what i am trying to get is

Customer ID     Order_id   Placed             Processed           Shipped            recieved
abc              123       8/1/2012 10:00Am    8/1/2012 10:30AM   8/1/2012 11:00AM   null
xyz              456       null                null               null               8/1/2012 12:00PM

i know the max number of columns i would need (the number of actions in other words) if that makes the query easier.

any help is much appreciated.

If you want real help, please post the CREATE statement for the table and a few Insert Statements for the data. It is hard to write working queries looking at the data. — srini.venigalla
– srini.venigalla, Commented Aug 21, 2012 at 20:04
What if there are two rows for processed, e.g. if an order gets returned and then re-shipped? — Aaron Bertrand
– Aaron Bertrand, Commented Aug 21, 2012 at 20:05

Taryn · Accepted Answer · 2012-08-21 20:10:44Z

You can use a PIVOT for this type of query.

SELECT *
FROM 
(
    SELECT Customer_id, Order_id, Action, Time_Stamp
    FROM yourTable
) x
PIVOT
(
   max(Time_Stamp)
   for action in ([Placed], [Processed], [Shipped], [received])
) p

See SQL Fiddle with Demo

The above query works great if you know the values of the columns you want to create. But if you do not, then you can use a dynamic pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(action) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT Customer_id, Order_id,' + @cols + ' from 
         (
            select Customer_id, Order_id, Action, Time_Stamp
            from test
         ) x
         pivot 
         (
            max(Time_Stamp)
             for action in(' + @cols + ')
         ) p '

execute(@query)

See SQL Fiddle with Demo

Glenn · Accepted Answer · 2012-08-21 20:10:57Z

0

If you are expected a fix set of states, and not multiple states per order, something like this may work for you:

SELECT customer_id, order_id
      ,MIN(CASE WHEN action = 'placed' THEN time_stamp ELSE NULL END) AS placed
      ,MIN(CASE WHEN action = 'processed' THEN time_stamp ELSE NULL END) AS processed
      ,MIN(CASE WHEN action = 'shipped' THEN time_stamp ELSE NULL END) AS shipped
      ,MIN(CASE WHEN action = 'received' THEN time_stamp ELSE NULL END) AS received
  FROM activity
  GROUP BY customer_id, order_id

answered Aug 21, 2012 at 20:10

Glenn

9,2102 gold badges44 silver badges56 bronze badges

Collectives™ on Stack Overflow

make multiple rows one row with additional columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related