Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Merge two rows in SQL

Posted on 2010-11-15
2
710 Views
Last Modified: 2012-05-10
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
Comment
Question by:abgsc
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 34135144
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 250 total points
ID: 34135157
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question