SQL specific rows to columns with True/False-CodePudding

I am trying to convert one row (companyname) to multiple columns, and have True/False display for each company indicating if a certain document is automated or not.

Current query:

select distinct 
    company.NAME as Company_Name, at.templateid as Template_ID, 
    doctemp.templatename as Template_Name, 
    case 
        when at.activeind = 0 then 'False'
        when at.activeind > 0 then 'True' 
    end as Automation_Enabled
from 
    opcoautomatedtemplate at
left join 
    cctl_operatingcompany company on company.ID = at.operatingcompany
left join 
    doctemplate doctemp on at.templateid = doctemp.templateid

Current query results:

Company_Name | Template_ID | Template_Name | Automation_Enabled
---------------------------------------------------------------
CompanyA     | 1           | Document A    | True
CompanyB     | 1           | Document A    | True
CompanyA     | 2           | Document B    | False
CompanyB     | 2           | Document B    | True
CompanyA     | 3           | Document C    | False
CompanyB     | 3           | Document C    | False

Desired result:

Template_ID | Template_Name | CompanyA | CompanyB  
-------------------------------------------------
1           | Document A    | True     | True
2           | Document B    | False    | True
3           | Document C    | False    | False

There are actually 7 different companies, and over 600 documents we check for, so the current result is a bit cumbersome to navigate. Also, we run our queries in Oracle, which can be a bit more temperamental than SQL Server. I am a bit new to SQL so any help is appreciated!

CodePudding user response：

You can achieve this by using PIVOT, e.g.:

WITH company_dets AS (SELECT 'CompanyA' company_name, 1 template_id, 'Document A' template_name, 1 activeind FROM dual UNION ALL
                      SELECT 'CompanyB' company_name, 1 template_id, 'Document A' template_name, 3 activeind FROM dual UNION ALL
                      SELECT 'CompanyA' company_name, 2 template_id, 'Document B' template_name, 0 activeind FROM dual UNION ALL
                      SELECT 'CompanyB' company_name, 2 template_id, 'Document B' template_name, 2 activeind FROM dual UNION ALL
                      SELECT 'CompanyA' company_name, 3 template_id, 'Document C' template_name, 0 activeind FROM dual UNION ALL
                      SELECT 'CompanyB' company_name, 3 template_id, 'Document C' template_name, 0 activeind FROM dual)
-- end of mimicking your data set in order to demo the pivot query
SELECT template_id,
       template_name,
       "CompanyA",
       "CompanyB"
FROM   (SELECT company_name,
               template_id,
               template_name,
               CASE WHEN activeind = 0 THEN 'False'
                    WHEN activeind > 0 THEN 'True'
               END automation_enabled
        FROM   company_dets) -- your query to generate the resultset to pivot would go in here
PIVOT (MAX(automation_enabled) FOR (company_name) IN ('CompanyA' AS "CompanyA", 'CompanyB' AS "CompanyB"));

TEMPLATE_ID TEMPLATE_NAME CompanyA CompanyB
----------- ------------- -------- --------
          1 Document A    True     True
          2 Document B    False    True
          3 Document C    False    False

Note that the pivot uses an aggregate function to derive the data - I've assumed that in your data, if you were to get two rows for the same company, template id and template name but they different automation states, you'd want to collapse the rows into one and report True rather than False. If you want to return False rather than True, use MIN instead of MAX