How to combine multiple rows into one row and multiple column in SQL Server?

Question

I have different tables through I made temp table and here is the result set of temp table:

     car_id  | car_type | status  | count   
     --------+----------+---------+------
     100421  |  1       |   1     |   9   
     100421  |  1       |   2     |   8   
     100421  |  1       |   3     |   3 
     100421  |  2       |   1     |   6   
     100421  |  2       |   2     |   8   
     100421  |  2       |   3     |   3 
     100422  |  1       |   1     |   5
     100422  |  1       |   2     |   8   
     100422  |  1       |   3     |   7

Here is the meaning of status column:

1 as sale
2 as purchase
3 as return

Now I want to show this result set as below

   car_id  | car_type | sale | purchase | return
   --------+----------+------+----------+----------
    100421 |  1       |  9   |   8      |  3
    100421 |  2       |  6   |   8      |  3
    100422 |  1       |  5   |   8      |  7

I tried but unable to generate this result set. Can anyone help?

Ullas · Accepted Answer · 2017-08-25 09:41:10Z

4

You can also use a CASE expression.

Query

select [car_id], [car_type],
max(case [status] when 1 then [count] end) as [sale],
max(case [status] when 2 then [count] end) as [purchase],
max(case [status] when 3 then [count] end) as [return]
from [your_table_name]
group by [car_id], [car_type]
order by [car_id];

answered Aug 25, 2017 at 9:41

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eid Morsy · Accepted Answer · 2017-08-25 08:24:28Z

3

Try this

select car_id  ,car_type, [1]  as Sale,[2] as Purchase,[3] as [return]
from (select car_id  , car_type ,  [status] ,[count] from tempTable)d
pivot(sum([count]) for [status] in([1],[2],[3]) ) as pvt

also you can remove the subquery if you don't have any condition like

select car_id  ,car_type, [1]  as Sale,[2] as Purchase,[3] as [return]
from tempTable d
pivot(sum([count]) for [status] in([1],[2],[3]) ) as pvt

edited Aug 25, 2017 at 8:24

answered Aug 25, 2017 at 8:10

Eid Morsy

9666 silver badges8 bronze badges

1 Comment

Eid Morsy Over a year ago

I'm sorry , I'v edit the query, after testing now its working

Collectives™ on Stack Overflow

How to combine multiple rows into one row and multiple column in SQL Server?

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related