CREATE Table #StockPrice
(Id INT
,StockName Varchar(100)
,ClientCode Varchar(20)
,Qty INT
,Price NUMERIC(28,4)
,IsBuySell INT NULL
)
INSERT INTO #StockPrice (Id,StockName,ClientCode,Qty,Price,IsBuySell)
VALUES (1,'Rel','AK47',200,1400.50,0) -- Buy
INSERT INTO #StockPrice (Id,StockName,ClientCode,Qty,Price,IsBuySell)
VALUES (2,'Rel','AK47',200,1400.50,1) -- Sell
INSERT INTO #StockPrice (Id,StockName,ClientCode,Qty,Price,IsBuySell)
VALUES (3,'TCS','AK45',100,2500.25,0) -- Buy
INSERT INTO #StockPrice (Id,StockName,ClientCode,Qty,Price,IsBuySell)
VALUES (4,'TCS','AK45',100,2400.50,0) -- Buy
INSERT INTO #StockPrice (Id,StockName,ClientCode,Qty,Price,IsBuySell)
VALUES (5,'SBI','AK44',1000,250.25,0) -- Buy
INSERT INTO #StockPrice (Id,StockName,ClientCode,Qty,Price,IsBuySell)
VALUES (6,'SBI','AK44',1000,240.50,1) -- Sell
INSERT INTO #StockPrice (Id,StockName,ClientCode,Qty,Price,IsBuySell)
VALUES (7,'Rel','AK47',50,1400.50,0) -- Buy
SELECT ClientCode ,StockName
,COUNT(CASE WHEN IsBuySell =0 THEN Id END) 'Buy Qty'
,COUNT(CASE WHEN IsBuySell =1 THEN Id END) 'Sell Qty'
FROM #StockPrice
GROUP BY ClientCode,StockName
DROP TABLE #StockPrice