Simple, pure Sql Query (getting rid of duplicate results)

Question

If anyone know a better title please, go ahead and change it.

I know that my attempt is mediacore at best so if you know a better aproach please share. I will try to keep it clear and simple:

My database table:

  ID  |      Category    |     Number   |      NumberInCategory    |

   1             A                31                101
   2             B                31                107
   3             C                31                121
   4             A                32                191
   5             A                33                165
   6             B                32                156
   7             C                32                127

What I need as output:

 Number  |         A         |        B        |         C       

   31             101                107                121
   32             191                156                127
   33             165                NULL               NULL

My attempt:

SELECT (SELECT DISTINCT(Number) FROM MYDB V WHERE F.ID = V.ID) AS Id, 
        (SELECT NumberInCategory FROM MYDB  V WHERE F.Number = V.Number AND Category =  'A' ) AS CategoryA  ,
        (SELECT NumberInCategory FROM MYDB V WHERE F.Number = V.Number AND Category =  'B' ) AS CategoryB,
        (SELECT NumberInCategory FROM MYDB  V WHERE F.Number = V.Number AND Category =  'C')  AS CategoryC
FROM MYDB F;

My result:

31|101|107|121
31|101|107|121
31|101|107|121
32|191|156|127
33|165||
32|191|156|127
32|191|156|127  

I just cant seem to find a way to get this working.

I anyone wants to help here is full script for https://www.tutorialspoint.com/execute_sql_online.php

BEGIN TRANSACTION;

/* Create a table called SAMPLE */
CREATE TABLE MYDB(Id integer PRIMARY KEY, Category text, Number integer, NumberInCategory integer);

/* Create few records in this table */
INSERT INTO MYDB VALUES(1,'A',31,101);
INSERT INTO MYDB VALUES(2,'B',31,107);
INSERT INTO MYDB VALUES(3,'C',31,121);
INSERT INTO MYDB VALUES(4,'A',32,191);
INSERT INTO MYDB VALUES(5,'A',33,165);
INSERT INTO MYDB VALUES(6,'B',32,156);
INSERT INTO MYDB VALUES(7,'C',32,127);
COMMIT;

/* Display all the records from the table */
SELECT * FROM MYDB;

SELECT (SELECT DISTINCT(Number) FROM MYDB V WHERE F.ID = V.ID) AS Id, 
        (SELECT NumberInCategory FROM MYDB  V WHERE F.Number = V.Number AND Category =  'A' ) AS CategoryA  ,
        (SELECT NumberInCategory FROM MYDB V WHERE F.Number = V.Number AND Category =  'B' ) AS CategoryB,
        (SELECT NumberInCategory FROM MYDB  V WHERE F.Number = V.Number AND Category =  'C')  AS CategoryC
FROM MYDB F;

EDIT Simple GROUP BY at the end does the job .____.

SELECT (SELECT Number FROM MYDB V WHERE F.ID = V.ID) AS Id, 
        (SELECT NumberInCategory FROM MYDB  V WHERE F.Number = V.Number AND Category =  'A' ) AS CategoryA  ,
        (SELECT NumberInCategory FROM MYDB V WHERE F.Number = V.Number AND Category =  'B' ) AS CategoryB,
        (SELECT NumberInCategory FROM MYDB  V WHERE F.Number = V.Number AND Category =  'C')  AS CategoryC
FROM MYDB F
GROUP BY NUMBER;

Result:

31|101|107|121                                                                                                                                                                                                                                         
32|191|156|127                                                                                                                                                                                                                                         
33|165|| 

Although the aproach from Accepted Answer (by @Gordon Linoff) is better.

Answer 1

Sql server supports a syntax called PIVOT:

SELECT number, [A],[B],[C]
FROM (select category, number, numberincategory from a) t
PIVOT
(
  SUM([numberincategory]) 
  FOR category IN ([A],[B],[C])
) AS pvt;

The basic format is:

SELECT 
  List,of,columns,that,will,form,row,headers, 
    List,of,row,values,that,form,column,headers
FROM (
  select query that pulls ONLY the data you need for 
  row headers, column headers, and the columns you want to summarise
)tablealias
PIVOT
(
  SUM/AVG/MIN/MAX/etc(column to summarise) 
  FOR category IN (List,of,row,values,that,form,column,headers)
) AS pvt;

Answer 2

You can use conditional aggregation:

select number,
       max(case when category = 'A' then numberincategory end) as a,
       max(case when category = 'B' then numberincategory end) as b,
       max(case when category = 'C' then numberincategory end) as c
from t
group by number;