TSQL Sum with type

Question

I'm writing using translate

Table1

id number type
1  6      111  
1  5      111
1  6      113
2  3      112
2  6      111

i need to sum group by value of "type" and "id" the result I want to see

RESULT Table

id  type111 type112 typ113
1   11      0       6
2   6       3       0

https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — jarlh, Feb 01 '18 at 09:06

score 0 · Answer 1 · answered Feb 01 '18 at 09:09

0

Try using SUM with CASE:

SELECT ID, 
SUM(CASE WHEN type = 111 THEN number END) AS type111,
SUM(CASE WHEN type = 112 THEN number END) AS type112,  
SUM(CASE WHEN type = 113 THEN number END) AS typE113,  
FROM TABLE1
GROUP BY ID

answered Feb 01 '18 at 09:09

Niranjan Rajawat

553
2
9

score 0 · Accepted Answer · answered Feb 01 '18 at 09:41

Another way to use pivoting:

SELECT  id,
        ISNULL([111],0) as Type111, 
        ISNULL([112],0) as Type112, 
        ISNULL([113],0) as Type113
FROM (
        SELECT  id, 
                number, 
                [type]   
        FROM Table1
    ) AS SourceTable  
PIVOT (SUM(number) FOR [type] IN ([111], [112], [113]) 
) AS PivotTable;

Output:

id          Type111     Type112     Type113
----------- ----------- ----------- -----------
1           11          0           6
2           6           3           0

(2 rows affected)

In case when the variety of types is high - use dynamic SQL to build and execute query.

TSQL Sum with type

2 Answers2