How to achieve this model

Question

I have a table like this

I am trying to achieve a table like this:

this was my query.

select name,token , sum(qty) [Qty],sum(amount)from #temp 
 group by token,name

tried like this..

 SELECT *
 FROM   (SELECT  name,
               qty,amt,
               Token
               FROM   #temp1
       ) AS D
       PIVOT(Sum(amt)
            FOR Token IN ([10],[20],[100],[40],[5])) AS P 

it is not working though. Any help will be appreciated.

Answer 1

I think you can achieve it by the following statement:

SELECT 
    [Name],
    SUM((CASE WHEN [Token] = 10 THEN [QTY] ELSE 0 END)) AS Token_10_QTY,
    SUM((CASE WHEN [Token] = 10 THEN [amt] ELSE 0 END)) AS Token_10_amt,
    SUM((CASE WHEN [Token] = 50 THEN [QTY] ELSE 0 END)) AS Token_50_QTY,
    SUM((CASE WHEN [Token] = 50 THEN [amt] ELSE 0 END)) AS Token_50_amt,
    SUM((CASE WHEN [Token] = 100 THEN [QTY] ELSE 0 END)) AS Token_100_QTY,
    SUM((CASE WHEN [Token] = 100 THEN [amt] ELSE 0 END)) AS Token_100_amt
FROM 
    #temp1
GROUP BY
    [Name]

assuming your columns QTY and amt are NOT NULL. I hope it will help you some way.