0

I would like to extract data from SQL table differently than how it is defined:

The table t_Plants has two columns where the first column is a hydro generator ID and the second column refers to the downstream hydro generator ID.

Plant_ID    Downstream_ID
1           3
2           3
3           4
4           6
5           6
6           NULL

I would like to write a query that gives me upstream generator the following output (since there are possibly more than 1 upstream reservoirs, the UpStream_Lkup is a some sort of a list:

Plant_ID     UpStream_Lkup    
 3            1,2    
 4            3   
 6            4,5

Thanks for the help

Yonas Tesfay Gebrekiros
  • 11
  • 2
  • Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) – Amira Bedhiafi Nov 07 '19 at 15:08

1 Answers1

0

You seem to want aggregation and string concatenation:

select Downstream_ID as Plant_ID,
       string_agg(Plant_Id, ',') within group (order by Plant_Id) as UpStream_Lkup
from t_plants p
where Downstream_ID IS NOT NULL 
group by Downstream_ID;

Note: string_agg() is a new function in SQL Server. In older versions, you'll need to use the XML version:

select dp.Downstream_ID,
       stuff( (select concat(',', p.Plant_Id) 
               from t_plant p
               where p.Downstream_ID = dp.Downstream_ID
               for xml path ('')
              ), 1, 1, ''
            ) as UpStream_Lkup
from (select distinct p.Downstream_ID
      from t_Plants p
     ) dp;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! The first one returned the error message 'The function 'string_agg' may not have a WITHIN GROUP clause.' although I am using SSMS 18. On the other hand the xml version worked fine. – Yonas Tesfay Gebrekiros Nov 07 '19 at 15:28
  • @YonasTesfayGebrekiros . . . That is a very strange error. Perhaps the compatibility level is set to an earlier version of SQL Server. – Gordon Linoff Nov 08 '19 at 02:48