CODEWITHSUNDEEP
sqlsql-server
Vins007
Vins007

Reputation: 23

SQL : How to get columns values aggregated and populate as new columns

I have a huge SQL which result similar to Table1. Now I need to get the result set as Table2. This is from SQL Server but we dont have DB access as typical ways, Instead we have a internal tool where we can submit 'select' SQL statement and it'll dump excel file with the dataset. How can I get the result as table2 format?

Note: Due to the nature of the tool, we can use only SQL commands, cannot create StoredProcedure, View and etc.

Can we do something like this? 

SELECT 'Columns' From (Original SQL command) WHERE xxx GROUPBY YYY

Table1

IDNameKey       |ID     |Name   |Location       |Type   |Active
================|=======|=======|===============|=======|=====
111             |11     |AAA    |LocA           |Type1  |No
222             |22     |BBB    |LocB           |Type2  |Yes
333             |33     |CCC    |LocA           |Type4  |No
444             |11     |AAA    |LocC           |Type2  |Yes
555             |55     |EEE    |LocB           |Type4  |No
666             |22     |BBB    |LocB           |Type2  |Yes

Table2 (Expected way)

ID      |Name   |Location |Type   |Active Count(Yes)  |Inactive Count(No)
========|=======|=========|=======|===================|=================
11      |AAA    |LocA     |Type1  |1                  |1
22      |BBB    |LocB     |Type2  |2                  |0
33      |CCC    |LocA     |Type4  |0                  |1
55      |EEE    |LocB     |Type4  |0                  |1

Upvotes: 0

Views: 49

Answers (2)

Jim Horn
Jim Horn

Reputation: 889

For starters, give this a whirl..

SELECT ID, Nave, Location, Type, 
   SUM(CASE Active WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 END) as [Active Count(Yes)],
   SUM(CASE Active WHEN 'No' THEN 1 WHEN 'Yes' THEN 0 END) as [Inactive Count(No)]
FROM Table1
GROUP BY ID, Nave, Location, Type

For a code and image-heavy tutorial on CASE blocks check out my article SQL Server CASE Solutions

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Use conditional sum

    SELECT ID, Name, Location, Type,
          SUM(CASE WHEN Active = 'Yes' THEN 1 ELSE 0 END) as [Active Count(Yes)],
          SUM(CASE WHEN Active = 'No' THEN 1 ELSE 0 END) as [Active Count(No)]

    From (Original SQL command) 
    GROUP BY ID, Name, Location, Type

Upvotes: 1

Related Questions