How to split a single query into two?

Question

I'm trying to figure out how to split a single query into two separate queries. The following two queries is what I want:

Query #1:

Label            Value          
TOTALSTUFF       100

Query #2:

Label           Value
NONSTUFF        50

Currently the query displays like this:

TOTALSTUFF     NONSTUFF
100            50

If someone would be so kind as to help me out regarding the pattern(s) I should look for when reading/reconstructing these types of queries it would be awesome, since there will be more I'd need to do this with. That may or may not be possible but I'm giving everything a shot at this point.

Here's the original query:

SELECT  FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0') 
AS 'TOTALSTUFF' ,FORMAT(SUM(c.countworkorders - 
countpositivecontact),'##,##0') AS 'NONSTUFF'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid 
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

Answer 1

Without knowing the logic in the query or the underlying dataset, the easiest (but not necessarily the best) way to do what you're asking is to use these 2 queries:

SELECT  'TOTALSTUFF' as 'Label', FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0') 
AS 'Value' 
FROM (SELECT DISTINCT
dpt.dateperiodtypeid 
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

and

SELECT 'NONSTUFF' as 'Label', FORMAT(SUM(c.countworkorders - 
countpositivecontact),'##,##0') AS 'Value'
FROM (SELECT DISTINCT
dpt.dateperiodtypeid 
,wo.workordercode
, 1 AS countworkorders
, wo.successfulcompletionind AS countpositivecontact
FROM (SELECT 'CALYEAR' AS dateperiodtypeid
UNION ALL
SELECT 'CALMONTH'
UNION ALL
SELECT 'ROLLBCKPM24MBYMAM') ddpt
JOIN wh.cpy_dateperiodtype_dim dpt
ON dpt.dateperiodtypeid = ddpt.dateperiodtypeid
JOIN wh.cpy_dateperiod_dim dp
ON dp.dateperiodtypekey = dpt.dateperiodtypekey
JOIN wh.mwm_workorder_fact wo
ON wo.createdatetime >= dp.startdate
AND cast(wo.createdatetime as Date) <= dp.enddate
JOIN wh.cpy_employee_dim e
ON e.employeekey = wo.assignedtokey
JOIN wh.cpy_employee_dim mgr
ON mgr.employeekey = e.managerkey
JOIN wh.cpy_org_dim o
ON o.orgid = e.orgid
WHERE wo.dnpind = 1) c
WHERE c.dateperiodtypeid = 'CALMONTH';

This is just using Column aliases as described here - https://msdn.microsoft.com/en-GB/library/ms176104.aspx

Edited to add a better explanation:

The change is in the columns section of the query. In your original you're requesting that SQL Server returns:

SELECT  FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0') 
AS 'TOTALSTUFF' ,FORMAT(SUM(c.countworkorders - 
countpositivecontact),'##,##0') AS 'NONSTUFF'

which is 2 columns, one named TOTALSTUFF and one named NONSTUFF. In the first of my reworked queries we request:

SELECT  'TOTALSTUFF' as 'Label', FORMAT(SUM(c."COUNTWORKORDERS"),'##,##0') 
AS 'Value' 

First we request a dummy column 'TOTALSTUFF' and alias it as 'Label'. Next we pull back the value that was aliased as TOTALSTUFF in your original query and this time alias it as 'Value'

The logic behind your 2 values TOTALSTUFF and NONSTUFF is the same, so I could just split out the columns in the column list.