Format Shift

Marksdasjkv 81 Reputation points
2022-12-15T03:27:06.087+00:00

I have a table like this:
Date Win/Loss
2017-07-12 win
2017-07-12 loss
2017-07-15 win
2017-07-15 loss

I want this result:
Date win loss
2017-07-12 1 1
2017-07-15 1 1

Can this be achieved?

Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

Accepted answer
  1. NikoXu-msft 1,916 Reputation points
    2022-12-15T06:25:15.187+00:00

    Hi @Marksdasjkv ,

    Try this query:

    ;with cte as   
    (  
    select *,ROW_NUMBER()over(partition by Date,[win/loss] order by Date) as rn from team  
    )  
    select * from cte pivot(max(rn)for [win/loss] in (win,loss))as pvt  
    

    Best regards,
    Niko

    ----------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


2 additional answers

Sort by: Most helpful
  1. Jingyang Li 5,896 Reputation points Volunteer Moderator
    2022-12-15T04:21:20.8+00:00
    Select [Date]   
    ,Sum(case when [Win/Loss]='win' then 1 else 0 end) [win]  
    ,Sum(case when [Win/Loss]='loss' then 1 else 0 end) [loss]  
    from yourtable   
    Group by [Date]  
    

  2. jose de jesus 141 Reputation points
    2022-12-15T04:28:28.663+00:00

    yes.
    you can use self join
    join on date

    Select t1.date,numberwin, numberloss from
    (
    select date, numberwin, from table1 where comment='win'
    ) as t1
    join
    (
    select date, number as numberloss, from table1 where comment='loss'
    ) as t2
    on t1.date=t2.date

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.