How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?

Question

Here is my T-SQL query

SELECT 
    ProductID,
    VendorID,
    ProductName= MAX(ProductName),
    VendorName = MAX(VendorName),
    IsActive = MAX(IsActive) # This brings error 
FROM ProductVendorAssoc 
GROUP BY  
    ProductID,
    VendorID

I want to apply GROUP BY only for the ProductID and VendorID fields, but need to populate the ProductID, VendorID, ProductName, VendorName, IsActive fields.

Here I used the agreggate function MAX(ProductName) to avoid ProductName in the group by list.

But the same trick is not working for BIT columns as operand data type bit is invalid for max operator.

How can i include BIT type column in SELECT part with out including it on the GROUP BY?

Update.

What should I need to do if i need to include an INT column like UserID in SELECT in the same way

Answer 1

Put a CASE expression in there, or convert it to int:

IsActive = MAX(CASE WHEN IsActive=1 THEN 1 ELSE 0 END)

or,

IsActive = MAX(CONVERT(int,IsActive))

You should also be aware, obviously, that this means that the values in the ProductName, VendorName and IsActive columns in the result set may all come from different rows in the base table.

---

If you want those three columns to actually all be from the same row (and assuming SQL Server 2005 or later), you'd do something like:

;With Numbered as (
    SELECT *,ROW_NUMBER() OVER (
        PARTITION BY ProductID,VendorID
        ORDER BY /* Something appropriate, or if we just want random... */ newid()) as rn
    FROM ProductVendorAssoc
)
select
    ProductID,
    VendorID,
    ProductName,
    VendorName,
    IsActive
FROM Numbered where rn=1