March 20, 2014 at 11:20 pm
hi friend i have small doubt in sql server please tell me how to solve this
table data look like below
table:emp
id , name, sal
B , abc, 100
A , abc, -30
p , abc, -40
s , abc, -20
T , abc , -10
B ,def , 200
A ,def , -90
p ,def , -10
s , def , -50
T ,def , -50
based on that table data i want output like below
id ,name , Toalamount(B) , adjust(A) ,payed(p) ,self(S) ,trans(T)
B ,abc , 100 , 0 ,0 ,0 ,0
A ,abc , 0 ,-30 ,0 ,0 ,0
p ,abc , 0 ,0 ,-40 ,0 ,0
s ,abc , 0 ,0 ,0 ,-20 ,0
T ,abc , 0 ,0 ,0 ,0 ,-10
B ,def , 200 ,0 ,0 ,0 ,0
A ,def , 0 ,-90 ,0 ,0 ,0
p ,def ,0 ,0 ,-50 ,0 ,0
s ,def ,0 ,0 ,0 ,-50 ,0
T ,def ,0 ,0 ,0 ,0 ,-50
please tell me query how to get required output using query in sql server .
March 20, 2014 at 11:32 pm
How do you get from one dataset to the other? What transformations are you doing?
Either my psychic powers are failing or you have left out a significant part of the logic behind the transformations.
Please read this article and follow the instructions if you need some help... without it we really can't help you.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 21, 2014 at 9:34 am
You're looking for CROSS TABS or PIVOT. Take a look at this article to know more about it: http://www.sqlservercentral.com/articles/T-SQL/63681/
Here's an example:
SELECT id , name,
SUM( CASE WHEN id = 'B' THEN sal ELSE 0 END) AS Toalamount(B),
SUM( CASE WHEN id = 'A' THEN sal ELSE 0 END) ASadjust(A)
--And so on
FROM emp
GROUP BY id , name
Note, be sure to have a Case Sensitive Collation if there's a difference between "B" and "b".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply