Find what letters are in a group of records in either SQL or SSRS

Question

I have the following data (I have a lot more rows this is just an example):

VALUE   Location             FLEET    REGION
P       Pocatello, ID        104       232
B       Pocatello, ID        104       232
B       Spokane, WA          107        232
B       Spokane, WA          107       232

In either reporting services or MSSQL I need to group by fleet. Afer it is grouped I want it to see what values each group contains.

If the group contains values P and B then its Production If the group contains B only then it is Reporting If the group contains P then its Productivity

What I want from this example is the following rows to be returned in the end.

VALUE         LOCATION           FLEET    REGION
Production     Pocatello,ID       104       232
Reporting      Spokane, WAS       107       232

Right now the SQL query reports the data at the top of my quesiton. I need to either do the grouping and calculation is MSSQL or SSRS either one will work but how do I go about doing it to get the data listed like I have right above.

Thanks!

Answer 1

Without a table definition(s), it's hard to say. You already have a complete duplicate in your sample data, which indicates that there is no Primary Key on the table. Is the location for each fleet always the same? What about the region? If this is actually a result set from another query that is joining tables then you should mention that and include the table definitions for the individual tables.

Here's my best guess as to something that might point you in the right direction:

SELECT
    CASE
        WHEN MIN(value) = 'B' THEN
            CASE
                WHEN MAX(value) = 'P' THEN 'Production'
                ELSE 'Reporting'
            END
        WHEN MAX(value) = 'P' THEN 'Productivity'
        ELSE NULL
    END,
    location,
    fleet,
    region
FROM
    Some_Table
GROUP BY
    location,
    fleet,
    region

This relies on their only being two possible values for "value" and is not easily adaptable otherwise.

Answer 2

You could group on the other columns, and check for the presence if P or B in a case statement:

declare @t table (value char(1), Location varchar(50), fleet int, region int)

insert @t values
    ('P',       'Pocatello, ID',        104,       232),
    ('B',       'Pocatello, ID',        104,       232),
    ('B',       'Spokane, WA',          107,       232),
    ('B',       'Spokane, WA',          107,       232);

select  case 
            when max(case when value = 'P' then 1 end) = 1 
                 and max(case when value = 'B' then 1 end) = 1 then 'Production' 
            when max(case when value = 'P' then 1 end) = 1 then 'Productivity' 
            when max(case when value = 'B' then 1 end) = 1 then 'Reporting' 
        end
,       location   
,       fleet
,       region
from    @t
group by
        location   
,       fleet
,       region

This prints:

(No column name)    location        fleet   region
Production          Pocatello, ID   104     232
Reporting           Spokane, WA     107     232

Answer 3

Select Fleet
    , Min(Case
        When ValueCount.PCount = 1 And ValueCount.BCount = 1 Then 'Production'
        When ValueCount.PCount = 1 And ValueCount.BCount = 0 Then 'Productivity'
        Else 'Reporting'
        End) As Value
    , Min(Location) As Location
    , Min(Region) As Region
From Table
    Join    (
            Select Fleet
                , Sum( Case When T2.Value = 'P' Then 1 Else 0 End ) As PCount
                , Sum( Case When T2.Value = 'B' Then 1 Else 0 End ) As BCount
            From Table As T2
            Where T2.Value In('P','B')
            Group By T2.Fleet
            ) As ValueCount
        On ValueCount.Fleet = Table.Fleet
Group By Fleet