Getting repeated values after use Group by clause

Question

I have a table like this one:

+------------+---------------+--------------------------+---------+
| GeneralKey |     Label     |         Extended         | OrderID |
+------------+---------------+--------------------------+---------+
|         63 | Take-Off      | Project Status           |       1 |
|         64 | Hold          | Project Status           |       3 |
|         65 | Pending       | Project Status           |       5 |
|         66 | Contracted    | Project Status           |       6 |
|         67 | Dead          | Project Status           |       7 |
|         86 | Pricing       | Project Status           |       4 |
|         99 | Bid Request   | Project Status           |       0 |
|        100 | Take-Off      | Design Contracted Status |       0 |
|        101 | Hold          | Design Contracted Status |       2 |
|        102 | Pricing       | Design Contracted Status |       3 |
|        103 | Issued        | Design Contracted Status |       4 |
|        104 | In-House      | Design Contracted Status |       5 |
|        105 | Shops/layouts | Project Status           |       2 |
|        106 | Shops/layouts | Design Contracted Status |       1 |
+------------+---------------+--------------------------+---------+

And I have stored procedure to use this table like:

BEGIN

        DECLARE @Parameter1 VARCHAR(50)
        DECLARE @Parameter2 VARCHAR(50);
        ;WITH CTE
            AS (SELECT
                    *
                   , ROW_NUMBER() OVER(ORDER BY
                                          (SELECT
                                                NULL
                                          )) AS [rn]
                    FROM STRING_SPLIT(@Extended , ','))
            SELECT
                 @Parameter1 = MAX(CASE
                                   WHEN [rn] = 1
                                   THEN VALUE
                                END)
                , @Parameter2 = MAX(CASE
                                   WHEN [rn] = 2
                                   THEN VALUE
                                END)
                 FROM [CTE]

        SELECT
             [GeneralKey]
            , [Label]
             FROM [General]
             WHERE [Extended] = (SELECT
                                   @Parameter1)
                  OR [Extended] = (SELECT
                                     @Parameter2)
             GROUP BY
                    [Label]
                   ,[GeneralKey]
                   , [OrderID]
             ORDER BY
                    [OrderID];
    END

The problem is when I execute it with exec instruction

usp_Get_StatusListByDesignType @Extended = 'Project Status,Design Contracted Status'

It returns repeated Label values:

+------------+---------------+
| GeneralKey |     Label     |
+------------+---------------+
|         99 | Bid Request   |
|        100 | Take-Off      |
|         63 | Take-Off      |
|        106 | Shops/layouts |
|        105 | Shops/layouts |
|        101 | Hold          |
|        102 | Pricing       |
|         64 | Hold          |
|        103 | Issued        |
|         86 | Pricing       |
|        104 | In-House      |
|         65 | Pending       |
|         66 | Contracted    |
|         67 | Dead          |
+------------+---------------+

As you can see I have repeated values(Label), GROUP BY clause isn't working on stored procedure. What am I doing wrong? Regards

UPDATE

As comments bellow I try to include GeneralKey with aggregate function like:

 SELECT
                 SUM([GeneralKey])
                , [Label]
                 FROM [General]
                 WHERE [Extended] IN (@Parameter1, @Parameter2)
                GROUP BY 
                [Label]
                ,[OrderID]
                 ORDER BY
                        [OrderID];

But I get same results

Answer 1

Whatever field you add to your GROUP BY is what your result set is going to be aggregated to. If one of those fields is more granular than you want, then it MUST be removed from your group by, otherwise your result set will take on that granularity.

In your case (your last attempt) you have OrderID in your GROUP BY. Because you have multiple OrderID for each Label your result set is going to be at the granularity of the OrderID when you desire it to be at the granularity of the Label field.

A simple query here to do what you want would be:

SELECT SUM(GeneralKey)
    ,Label
FROM General
WHERE Extended IN (
        @Parameter1
        ,@Parameter2
        )
GROUP BY Label;

If you need OrderID to be a part of this (perhaps for ordering) then you'll need to aggregate by it:

SELECT SUM(GeneralKey)
    ,Label
FROM General
WHERE Extended IN (
        @Parameter1
        ,@Parameter2
        )
GROUP BY Label
ORDER BY MAX(OrderId)

ORDER BY is one of the last steps the RDBMS hits when processing the data. It takes place AFTER the GROUP BY so you'll have to tell it how to aggregate the field by which you want to ORDER BY if that field is at a deeper level of granularity than you desire in your result set.

Answer 2

Here is how you can derive a comma delimited list of matches into a calculated field.

DECLARE @T TABLE(GeneralKey INT, Label NVARCHAR(50), OrderID INT, Extended NVARCHAR(50))
INSERT @T VALUES (99,'Bid-Request',1,'Project Status'),(100,'Take-Off',3,'Project Status'),(63,'Bid-Request',5,'Project Status'),(199,'Bid-Request',10,'Design Contracted Status')

DECLARE @Extended NVARCHAR(50) = 'Project Status,Design Contracted Status'

DECLARE @Parameter1 VARCHAR(50) ='Project Status'
DECLARE @Parameter2 VARCHAR(50) = 'Design Contracted Status'

SELECT
    T.Label,
    MInOrderId = MIN(OrderID),
    MaxOrderID = MAX(OrderID),
    GeneralKeys=
        STUFF((
            SELECT ', ' + CONVERT(VARCHAR(10), GeneralKey)
            FROM @T This WHERE This.Label = T.Label AND This.Extended IN(@Parameter1,@Parameter2)
            FOR XML PATH ('')) , 1, 1, ''),
    OrderIds=
        STUFF((
            SELECT ', ' + CONVERT(VARCHAR(10), OrderID)
            FROM @T This WHERE This.Label = T.Label AND This.Extended IN(@Parameter1,@Parameter2)
            FOR XML PATH ('')) , 1, 1, ''),      
    ExtendedIds=
        STUFF((
            SELECT DISTINCT ', ' +  Extended
            FROM @T This WHERE This.Label = T.Label AND This.Extended IN(@Parameter1,@Parameter2)
            FOR XML PATH ('')) , 1, 1, '')   
FROM 
    @T T
WHERE 
    Extended IN (@Parameter1,@Parameter2)
GROUP BY
    T.Label
ORDER BY
   MIN(OrderID)