[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

Merge two rows in SQL

Hi,
we have created a view that shows info from many different tables joined together. the output look like this
SELECT * from view_Analysts
AS-IS result:
id  date              analyst  type    
1   10/10/2009   Smith     Lunch  
1   10/10/2009   Hall        Lunch  
2   12/11/2009   Smith     Lunch
3   13/11/2009   Jackson Group
3   13/11/2009   Harris     Group

TO-BE result
id  date              analyst              type
1   10/10/2009   Smith+Hall         Lunch
2   12/11/2009   Smith                 Lunch
3   13/11/2009   Jackson+Harris Group

When all columns, except analyst, is the same, then mergethe two rows into one row with a '+' sign in analyst column

How can I write my sql to get TO-BE output?
0
abgsc
Asked:
abgsc
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This would do:
select distinct t1.id, t1.date, t1.type,
substring((SELECT '+' + t2.analyst FROM view_Analysts t2 where t1.id = t2.id for XML PATH ('')), 2, 1000) analyst
FROM view_Analysts t1

Open in new window

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now