CODEWITHSUNDEEP
sql
axna2000
axna2000

Reputation: 29

split data with query in vb6

I have data in my table like this

TrNo | ManRef | Transport
001    ASC1     30000
001    ASC1     15000
002    ASC2     30000

and i want the data become like this

TrNo | ManRef | Transport 1 | Transport 2
001    ASC1     30000          15000
002    ASC2     30000           -

so..any one can help me, how to query this table...pleaseee

Upvotes: 1

Views: 155

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Assuming you don't need to go Dynamic, a simple conditional aggregation in concert with Row_Number() should do the trick

Example

Select TrNo
      ,ManRef
      ,sum(case when RN=1 then Transport else 0 end) as Transport1 
      ,sum(case when RN=2 then Transport else 0 end) as Transport2 
      ,sum(case when RN=3 then Transport else 0 end) as Transport3 
      ,sum(case when RN=4 then Transport else 0 end) as Transport4 
      ,sum(case when RN=5 then Transport else 0 end) as Transport5
      ,sum(case when RN=6 then Transport else 0 end) as Transport6
 From (
        Select *
              ,Row_Number() over (Partition By TrNo,ManRef Order By Transport Desc) as RN 
         From YourTable
      ) A
 Group By TrNo,ManRef

Validated Results

enter image description here

Upvotes: 1

Related Questions