Saliceran Saliceran - 1 year ago 66
SQL Question

How to generate a flag to determine which database a table is in?

I have a table that consists of two columns, DatabaseName and TableName. Some tables are in more than one database. What I would like to do is write a query that returns distinct TableName and three new columns that contain a

if it is in the corresponding database or an
if it does not. To clarify, a table can appear more than once in the TableName column but will have something different in the DatabaseName column for each entry. Basically I want a list of the tables and an easy way to see which databases it is in.

I tried something like this:

sel distinct tablename, case when databasename='DB1' then 'Y' else 'N' end as DB1,
case when databasename='DB2' then 'Y' else 'N' end as DB2,
case when databasename='DB3' then 'Y' else 'N' end as DB3
from db.table_analysis

but it of course doesn't combine the rows with the same tablename together.

Answer Source
SELECT tablename, 
       CASE WHEN COUNT( CASE WHEN databasename='DB1' THEN 1 END) > 0 
            THEN 'Y'
            ELSE 'N'
       END as DB1,
       CASE WHEN COUNT( CASE WHEN databasename='DB2' THEN 1 END) > 0 
            THEN 'Y'
            ELSE 'N'
       END as DB2,
       CASE WHEN COUNT( CASE WHEN databasename='DB3' THEN 1 END) > 0 
            THEN 'Y'
            ELSE 'N'
       END as DB3
FROM db.table_analysis
GROUP BY tablename
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download