0

Input : Table A

company | name    | rank
---------------------------     
ABC     | Tom     | 1
ABC     | Harry   | 3
ABC     | Robert  | 2
DEF     | Chris   | 2
DEF     | Dwayne  | 1

Output : Table B

company |     names             | ranks
--------------------------------------------          
ABC     |  Tom, Harry, Robert   | 1,3,2
DEF     |  Chris, Dwayne        | 2,1
--------------------------------------------

I want to concatenate name and rank row-wise (based on company) in Table A in columns names and ranks in Table B such that for every row in Table B, the order of names in names corresponds to the order of ranks in ranks.

I'm using the T-SQL query below (Try it out here):

SELECT DISTINCT company, 
(SELECT name + ','
 FROM A as t2
 WHERE t2.company = t1.company
 ORDER BY rank
 FOR XML PATH('')) as names,
(SELECT CAST(rank AS VARCHAR) + ',' 
 FROM A as t2
 WHERE t2.company = t1.company
 ORDER BY rank
 FOR XML PATH('')
) as ranks
FROM A as t1;

Can this be optimized to use just one SELECT for names and ranks instead of two SELECT used above?

EDIT :

Using Recursive CTE (Try it out here): 

WITH CTE (company, names, ranks, rank)
AS 
(
  SELECT company, CAST(name AS VARCHAR(8000)) names, CAST(rank AS VARCHAR(512)) ranks, rank
  FROM A
  WHERE rank = 1
  UNION ALL
  SELECT t1.company, CAST(t1.names + ',' + t2.name AS VARCHAR (8000)), CAST(t1.ranks + ',' + CAST(t2.rank AS VARCHAR) AS VARCHAR(512)), t2.rank
  FROM CTE as t1 INNER JOIN A as t2
  ON t1.company = t2.company AND t1.rank + 1 = t2.rank
), 
CTE2
AS
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY company ORDER BY rank DESC) rn FROM CTE
) SELECT company, names, ranks FROM CTE2 WHERE rn = 1
Bharat Khatri
  • 1,367
  • 2
  • 16
  • 27
  • @JamesZ How would I ensure that a name in `names` corresponds to a rank in `ranks` in Table *B*? – Bharat Khatri Jul 23 '15 at 14:15
  • In general, you want to avoid code like this in SQL - you're trying to store multiple values in a single cell. If you insist on handling this on the database level, take a look at `xml` columns, CLR functions or recursive common table expressions, for example. The `xml` solution would probably be preferred - it allows you to keep the original layout of the data instead of relying on ordering. You can then use that Xml in your presentation layer to build whatever you need. – Luaan Jul 23 '15 at 14:17
  • Are you familiar with `.net` and `SQL CLR` functions? It's better to create a custom CLR aggregate function for such tasks. All you need can be found here - https://msdn.microsoft.com/en-us/library/ff878119.aspx – gotqn Jul 23 '15 at 14:18

0 Answers0