CODEWITHSUNDEEP
sqlsql-server
K09
K09

Reputation: 201

Returning all 'True' cases from Table

I have a database table, 'Reports'. Each column in this table is of type bit. So if value 1 (i.e. true) then report is required.

I have a Stored Procedure which is used to populate a temp table with ALL reports marked as true.

What is the best way to do this? My CASE statement returns only the first TRUE value, when what I want is all cases where report is TRUE.

Thanks!

DECLARE @RequiredReports as table
(
    Report nvarchar(150)
)
INSERT INTO @RequiredReports
(
    Report
)
SELECT 
    CASE 
        WHEN r.ReportCountry = 1 THEN 'Country Report'
        WHEN r.ReportPerson = 1 THEN 'Person Report'
        WHEN r.ReportProfession = 1 THEN 'Profession Report'
        WHEN r.ReportAge = 1 THEN 'Age Report'
    END 
FROM dbo.Reports r

Upvotes: 1

Views: 112

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

You might try using UNPIVOT:

declare @Reports table (
[User] nvarchar(250) not null,
ReportCountry bit not null,
ReportPerson bit not null,
ReportProfession bit not null,
ReportAge bit not null
)
insert into @Reports ([User],ReportCountry,ReportPerson,ReportProfession,ReportAge)
select 'Damien',1,0,1,0

select
    *
from
    @Reports unpivot (RunReport for ReportName in (ReportCountry,ReportPerson,ReportProfession,ReportAge)) r

Result is:

User    RunReport   ReportName
Damien  1   ReportCountry
Damien  0   ReportPerson
Damien  1   ReportProfession
Damien  0   ReportAge

And then you can treat it as a normal table source for further querying/filtering.

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use cross apply like this:

select T.Name
from dbo.Reports as R
  cross apply (select R.ReportCountry, 'Country Report' union all
               select R.ReportPerson, 'Person Report' union all
               select R.ReportProfession, 'Profession Report' union all
               select R.ReportAge, 'Age Report') as T(Active, Name)
where T.Active = 1

https://data.stackexchange.com/stackoverflow/query/61227/unpivot-reports

In SQL Server 2008 and later you can use values instead of union all.

select T.Name
from dbo.Reports as R
  cross apply (values (R.ReportCountry, 'Country Report'),
                      (R.ReportPerson, 'Person Report'),
                      (R.ReportProfession, 'Profession Report'),
                      (R.ReportAge, 'Age Report')) as T(Active, Name)
where T.Active = 1

Upvotes: 5

Related Questions