Getting the max value or combine data for duplicate values

Question

This is linked to my previous Qn

Table A: 
ID  Rank Name 
1   100  Name1 
1    45  Name2 
2    60  Name3 
2    42  Name4 
2    88 Name5 
3    50 name6
3    50 name7

Table B: 
ID FileName 
1  fn1 
2  fn2 
3  fn3

What I want is

1 fn1 name1 
2 fn2 name5 
3 fn3  name6,name7

Here is my code that doesnt deal with the duplciate ranks above, so I get two rows for value 3.

select B.*,A.Name
LEFT JOIN ( SELECT A.Id, MAX(A.Rank)as Rank 
    FROM A 
    GROUP BY A.Id
    ) AS NewA 
            JOIN A 
                on A.Rank = NewA.Rank
                    AND A.ID = NewA.Id
    on NewA.ID = B.ID

How do I modify this to give me A.name seperated by comma when the ranks are same for a Id?

Thanks

Sql Server Group Concatenation - it's a frequent SO question: http://stackoverflow.com/questions/941103/concat-groups-in-sql-server — J Cooper, Feb 28 '12 at 03:24
@JCooper: I dont have a problem concatening stings for values. I got how to do it. My weakpoint is when I ahve to combine data with another table. — user393148, Feb 28 '12 at 03:27
Wait, shouldnt this be: 1|fn1|name1,name2 --- 2|fn2|name3,name4,name5 --- 3|fn3|name6,name7 ? I just want to make sure I am creating the appropriate solution — Justin Pihony, Feb 28 '12 at 04:34
No. Combine Only when when I have max conflict. otherwise I choose the alias for the max rank. Like for Ids 1 and 2. Id 3 has rank duplicate, then combine the two names. — user393148, Feb 28 '12 at 04:35

score 3 · Answer 1 · edited Jan 18 '21 at 12:30

3

select B.ID,
       B.[FileName],
       stuff((select ',' + A1.name
              from TableA as A1
              where B.ID = A1.ID and
                    A1.[Rank] = (select max(A2.[Rank])
                                 from TableA as A2                   
                                 where A1.ID = A2.ID)
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as Names
from TableB as B

Try on SE-Data

edited Jan 18 '21 at 12:30

Community

1
1

answered Feb 28 '12 at 06:53

Mikael Eriksson

136,425
22
210
281

+1 . Thanks Mikael. I tried this solution as well. It worked for me. I already had integrated the below solution into my project, so I will go with this. Too bad I cant pick two answers. :) – user393148 Feb 28 '12 at 20:09
@AaronBertrand : Actually it so happened that I had seen another issue with my code integrated with the #temp solution. So I switched to Mikaels solution. – user393148 Feb 29 '12 at 19:19

Aaron Bertrand · Answer 2 · 2022-01-23T18:36:14.453

;WITH x AS
(
    SELECT ID, [Rank], Name, 
      rn = DENSE_RANK() OVER (PARTITION BY ID ORDER BY [Rank] DESC)
      FROM dbo.TableA
), y AS
(
  SELECT x.ID, x.Name, f.[Filename]
    FROM x INNER JOIN dbo.TableB AS f
    ON x.ID = f.ID WHERE x.rn = 1
)
SELECT ID, [Filename], Names = STUFF((SELECT ',' + name
    FROM y AS y2 WHERE y.ID = y2.ID
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, '')
FROM y GROUP BY ID, [Filename]
ORDER BY ID;

Justin Pihony · Accepted Answer · 2012-02-28T04:50:53.203

0

You will need to use a CTE to flatten this out.

This should plug directly into SQL and work with the data given as an example. Obviously, you will need to update the queries for your real data since this is probably not your real schema

create table #tableA (ID int, [rank] int, name varchar(max))
insert into #tableA values(1,100,'Name1')
insert into #tableA values(1,45,'Name2')
insert into #tableA values(2,60,'Name3')
insert into #tableA values(2,42,'Name4')
insert into #tableA values(2,88,'Name5')
insert into #tableA values(3,50,'Name6')
insert into #tableA values(3,50,'Name7')

create table #tableB (ID int, [FileName] varchar(max))
insert into #tableB values(1,'fn1')
insert into #tableB values(2,'fn2')
insert into #tableB values(3,'fn3')

SELECT B.*,A.Name, ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY A.[rank]) AS RowNum
INTO #RankedTable
FROM #tableB as B
    LEFT JOIN ( 
        SELECT A.Id, MAX(A.Rank)as Rank 
        FROM #tableA AS A 
        GROUP BY A.Id
        ) AS NewA 
    JOIN #tableA AS A
        on A.Rank = NewA.Rank
            AND A.ID = NewA.Id
    on NewA.ID = B.ID 

;WITH ConcatenationCTE (ID, [FileName], Name, RowNum)
AS
(
  SELECT ID, [FileName], Name, RowNum 
  FROM #RankedTable
  WHERE RowNum = 1
  UNION ALL
  SELECT #RankedTable.ID, #RankedTable.[FileName],
          ConcatenationCTE.Name + ',' + #RankedTable.Name AS Name, 
          #RankedTable.RowNum 
  FROM #RankedTable
      JOIN ConcatenationCTE 
          ON ConcatenationCTE.ID = #RankedTable.ID 
              AND ConcatenationCTE.RowNum +1 = #RankedTable.RowNum
)
SELECT ConcatenationCTE.ID, [FileName], Name 
FROM ConcatenationCTE 
        JOIN 
     (SELECT ID, MAX(RowNum) AS RowNum 
      FROM ConcatenationCTE GROUP BY ID) AS FinalValues 
        ON FinalValues.ID = ConcatenationCTE.ID 
            AND FinalValues.RowNum = ConcatenationCTE.RowNum

edited Feb 28 '12 at 04:50

answered Feb 28 '12 at 03:21

Justin Pihony

66,056
18
147
180

OK, I tested my query I just updated against a dummy set of data and it worked. Let me know if any of it does not work or make sense. As always, show your appreciation with an upvote and answer acceptance :) – Justin Pihony Feb 28 '12 at 03:56
I will. Certainly. Let me give this a try. Thanks. – user393148 Feb 28 '12 at 03:59
Actually the query I use in the question above is part of one big select query from multiple tables. I am not sure if I can fit this in. But let me give it a try. – user393148 Feb 28 '12 at 04:07
You could just dump your data into a temp table to be modified. – Justin Pihony Feb 28 '12 at 04:08
Is there a way to merge above logic into my initial code. I tried a part of this into my code, but I am pretty sure I didnt do it right. because I dont see the rows for the other query. The other sub-queries in my big query are just selects. I am not sure. It would be of great help, if I can keep the above original code I have.Thanks – user393148 Feb 28 '12 at 04:22
Updated, it didnt take too much, actually. If your real query is that complicated and you cannot plug this in, then you may need to provide something closer to the actual query. Because, if you take my updated code above and plug it directly into SQL, it will work exactly as you requested. – Justin Pihony Feb 28 '12 at 04:52
I tried this with replacing my table schema. It wasnt difficult. But I get the following error. Types don't match between the anchor and the recursive part in column "Name" of recursive query "ConcatenationCTE". How do I do this type cast? – user393148 Feb 28 '12 at 05:54
SELECT ID, [FileName], CAST(Name AS VARCHAR(MAX)), RowNum.... for the anchor part of the CTE. (The first query with ...WHERE RowNum = 1). – Justin Pihony Feb 28 '12 at 14:30
Thanks Justin. I tried this with some modifications and it worked on my data as well. Appreciate a lot. – user393148 Feb 28 '12 at 20:08

Getting the max value or combine data for duplicate values

3 Answers3