CODEWITHSUNDEEP
sqlsql-server-2008
user1724737
user1724737

Reputation: 23

Two Rows into one row in SQL

I had an output for the following like this in sQL server 2008.

select ItemCode,
case when trntype = 'Issued' then sum(qty) end as issuedqty,
case  when trntype = 'Received' then sum(qty) end as receievedqty
from  [View_New]
group by ItemCode,trntype
Order by itemcode 

Code            Recd Qty   Issued Qty
--------------------------------------
10CMSQURSET     NULL       2.0000
10CMSQURSET     56.0000    NULL

How will i display these rows into one Row:

Code            Recd Qty   Issued Qty
--------------------------------------
10CMSQURSET 56.0000     2.0000

Please help

Upvotes: 2

Views: 183

Answers (2)

John Woo
John Woo

Reputation: 263693

another way is to use PIVOT() function

SELECT  itemCode,
        Received AS RECEIEVEDQTY,
        Issued AS ISSUEDQTY
FROM
        (
            SELECT  itemCode, trntype, qty
            FROM    View_new
        ) dta
        PIVOT
        (
            SUM(qty)
            FOR trntype IN ([Issued], [Received])
        ) pvt

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

Group only by item code and put the CASE inside the SUM:

select ItemCode,
   sum(case when trntype = 'Issued' then qty else 0 end) as issuedqty,
   sum(case  when trntype = 'Received' then qty else 0 end) as receievedqty
from  [View_New]
group by ItemCode
Order by itemcode

Upvotes: 0

Related Questions