Click here to Skip to main content
14,641,269 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi I have some values in list like

unit quantity datasource
1      10     manual
1      20     system 


I want to show it as

unit manual_quantity  system_quantity 
1     10               20 


How to do this ?

What I have tried:

Can Union server this ?

SELECT WITH DATA SOURCE MANUAL 
UNION 
SELECT WITH DATA SOURCE SYSTEM


but then how can I be sure they belong to same Unit.

else Self join ?
Posted
Updated 16-Jun-16 2:31am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Seems, you want to pivot data...

There's 2 ways to achieve that using SQL:
1)
SELECT unit, SUM(CASE WHEN datasource = 'manual' THEN quantity ELSE 0 END) AS manual_quantity,
             SUM(CASE WHEN datasource = 'system' THEN quantity ELSE 0 END) AS system_quantity
FROM table1 
GROUP BY unit

2)
SELECT unit, [manual], [system]
FROM (
    SELECT *
    FROM table1
) AS DT
PIVOT(SUM(quantity) FOR datasource IN([manual], [system])) AS pt
   
Comments
AlwzLearning 27-Jun-16 19:38pm
   
Can we do Pivot with LINQ to SQL ?
Maciej Los 28-Jun-16 0:04am
   
Yes!
Rate this:
Please Sign up or sign in to vote.

Solution 2

You can also try below query , its almost similar to above one but derived table is not required
Select Unit,Manual AS Manual_Quantity,System AS System_Quantity
FROM #TEMP 
PIVOT
(
SUM(quantity) FOR Datasource IN (Manual,[System])
) AS A
   
Comments
Maciej Los 18-Jun-16 5:01am
   
Good point! A 5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100