Algorithm to sum over a column with randomly selecting data from another column

Question

I have a table like this:

CREATE TABLE Table1
    ([IdeaNr] int, [SubmitterName] varchar(4), [SubmitterDepartment] varchar(4))
;

INSERT INTO Table1
    ([IdeaNr], [SubmitterName], [SubmitterDepartment])
VALUES
    (1, 'Joe', 'Org1'),
    (1, 'Bill', 'Org2'),
    (1, 'Kate', 'Org1'),
    (1, 'Tom', 'Org3'),
    (2, 'Sue', 'Org2'),
    (3, 'Bill', 'Org2'),
    (3, 'Fred', 'Org1'),
    (4, 'Ted', 'Org3'),
    (4, 'Kate', 'Org1'),
    (4, 'Hank', 'Org3')
;

I want to get the following result from a query:

IdeaNr  SubmitterCount   SubmitterRndName   SubmitterRndDepartment
1       4                Joe or ...         Org1 (if Joe)
2       1                Sue                Org2
3       2                Bill or ...        Org2 (if Bill)
4       3                Ted or ...         Org3 (if Ted)

I tried many things with all types of JOINs of table 1 with itself, views and GROUP BY, for example:

SELECT COUNT(IdeaNr) AS SubmitterCount,IdeaNr,SubmitterName,SubmitterDepartment
FROM Table1
GROUP BY IdeaNr,SubmitterName,SubmitterDepartment

I think the problem is to create an algorithm that takes only the first (or random) name and department that appear in the IdeaNr group. Obviously, you can be misleading in interpreting such data, for example. g .:

• Org1 has 2 ideas
• Org2 has 1 idea
• Org3 has 1 idea

But this type of "incorrect averaging" is suitable for the task. You can help?

EDIT: Expected Result of Discussion. The desired result has changed to:

IdeaNr  SubmitterCount   SubmitterRndName   SubmitterRndDepartment
1       4                Joe, Bill, ...     GroupIdea
2       1                Sue                Org2
3       2                Bill, Fred         GroupIdea
4       3                Ted, ...           GroupIdea

Answer 1

:

DECLARE @Table1 TABLE ([IdeaNr] int, [SubmitterName] varchar(4), [SubmitterDepartment] varchar(4));
INSERT INTO @Table1([IdeaNr], [SubmitterName], [SubmitterDepartment])
VALUES
    (1, 'Joe', 'Org1'),
    (1, 'Bill', 'Org2'),
    (1, 'Kate', 'Org1'),
    (1, 'Tom', 'Org3'),
    (2, 'Sue', 'Org2'),
    (3, 'Bill', 'Org2'),
    (3, 'Fred', 'Org1'),
    (4, 'Ted', 'Org3'),
    (4, 'Kate', 'Org1'),
    (4, 'Hank', 'Org3');

SELECT x.IdeaNr
      ,Count(x.IdeaNr)
      ,MAX(Submitter.SubmitterName) AS SubmitterRndName
      ,MAX(Submitter.SubmitterDepartment) AS SubmitterRndDepartment
FROM @Table1 AS x 
CROSS APPLY
(
    SELECT TOP 1 SubmitterName, SubmitterDepartment
    FROM @Table1 AS y 
    WHERE y.IdeaNr=x.IdeaNr
) AS Submitter
GROUP BY x.IdeaNr

, , :

SELECT x.IdeaNr
      ,Count(x.IdeaNr)
      ,STUFF(
            (
            SELECT ', ' + y.SubmitterName --maybe with DISTINCT
            FROM @Table1 AS y
            WHERE y.IdeaNr=x.IdeaNr
            FOR XML PATH('')
            ),1,2,'') AS AllSubmitters
      ,STUFF(
            (
            SELECT ', ' + z.SubmitterDepartment --maybe with DISTINCT
            FROM @Table1 AS z
            WHERE z.IdeaNr=x.IdeaNr
            FOR XML PATH('')
            ),1,2,'') AS AllDepartments
FROM @Table1 AS x 
GROUP BY x.IdeaNr

IdeaNr                  AllSubmitters              AllDepartments
1           4           Joe, Bill, Kate, Tom       Org1, Org2, Org1, Org3
2           1           Sue                        Org2
3           2           Bill, Fred                 Org2, Org1
4           3           Ted, Kate, Hank            Org3, Org1, Org3

EDIT: :

SELECT x.IdeaNr
      ,COUNT(x.IdeaNr)
      ,STUFF(
            (
            SELECT DISTINCT ', ' + y.SubmitterName 
            FROM @Table1 AS y
            WHERE y.IdeaNr=x.IdeaNr
            FOR XML PATH('')
            ),1,2,'') AS AllSubmitters
      ,CASE WHEN COUNT(x.IdeaNr)=1 THEN (SELECT TOP 1 z.SubmitterDepartment FROM @Table1 AS z WHERE z.IdeaNr=x.IdeaNr)
            ELSE 'GroupIdea' END AS Departments
FROM @Table1 AS x 
GROUP BY x.IdeaNr

Answer 2

, top-N-per-group. SQL Server CROSS APPLY.

SQL Fiddle

WITH
CTE
AS
(
    SELECT
        IdeaNr
        ,COUNT(*) AS SubmitterCount
    FROM @Table1
    GROUP BY IdeaNr
)
SELECT
    CTE.IdeaNr
    ,CTE.SubmitterCount
    ,CA.SubmitterName
    ,CA.SubmitterDepartment
FROM
    CTE
    CROSS APPLY
    (
        SELECT TOP(1)
            T.SubmitterName
            ,T.SubmitterDepartment
        FROM @Table1 AS T
        WHERE T.IdeaNr = CTE.IdeaNr

        --ORDER BY T.SubmitterName
        --ORDER BY T.SubmitterDepartment
        --ORDER BY CRYPT_GEN_RANDOM(4)

    ) AS CA
ORDER BY CTE.IdeaNr;

ORDER BY CROSS APPLY, "" . , . , , , , , .

IdeaNr, ORDER BY ..

, ORDER BY CRYPT_GEN_RANDOM(4).

ORDER BY, - :

IdeaNr    SubmitterCount    SubmitterName    SubmitterDepartment
1         4                 Joe              Org1
2         1                 Sue              Org2
3         2                 Bill             Org2
4         3                 Ted              Org3

, "" IdeaNr , . , , ORDER BY. IdeaNr , , - . , ID int IDENTITY , , ORDER BY ID DESC .

SQL Fiddle, , .