• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

how to merge duplicated records?

I have a query for a report in MS SQL 2008 data warehouse and have the following result

license_number     customer_name       description      group
12323                      walmart                  This is good     sales
-------------------------------------------------------------------------------------------------------
23456                      Walgreen                 so-so              marketing
----------------------------------------------------------------------------------------------------------
34567                      Go-Go                      improved       Eng
------------------------------------------------------------------------------------------------------------
12323                      walmart                  This is bad       sales
-------------------------------------------------------------------------------------------------------------
23456                      Walgreen                 go to there     marketing        
--------------------------------------------------------------------------------------------------------------
I want the result to be like below


license_number     customer_name       description      group
--------------------------------------------------------------------------------------------------------
12323                      walmart                  This is good     sales
                                                               This is bad      
----------------------------------------------------------------------------------------------------------
23456                      Walgreen                 so-so              marketing
                                                                go to there              
---------------------------------------------------------------------------------------------------------
34567                      Go-Go                      improved       Eng
------------------------------------------------------------------------------------------------------------
0
wasabi3689
Asked:
wasabi3689
  • 5
  • 5
5 Solutions
 
arnoldCommented:
You would need to separate into two tables, or the description column has to be large enough.
You would need to encode the new line.

You probably should use a transition table.
0
 
wasabi3689Author Commented:
how? can you provide an example?
0
 
arnoldCommented:
The issue is that you want to enforce unique license numbers and currently you have entries with multiples?

http://stackoverflow.com/questions/11301846/sql-update-statement-combining-multiple-rows-into-one-column

The encoding part will depend on what you are using to display the data, web based or other application.

Separating with a one to many relationship would simplify things.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
wasabi3689Author Commented:
This is for data warehouse report, which is web-based report.
0
 
arnoldCommented:
You could order by license_number and within the web based app handle the data being passed to the browser.
0
 
wasabi3689Author Commented:
Yes, in my query, I made order by license_number, I already have the query output format like below

license_number     customer_name       description      group
--------------------------------------------------------------------------------------------------------
12323                      walmart                  This is good     sales
12323                      walmart                 This is bad       sales
23456                      Walgreen                 so-so              marketing
23456                      Walgreen                 go to there    marketing
34567                      Go-Go                      improved       Eng
------------------------------------------------------------------------------------------------------------

I want to make look like below


license_number     customer_name       description      group
--------------------------------------------------------------------------------------------------------
12323                      walmart                  This is good     sales
                                                               This is bad      
----------------------------------------------------------------------------------------------------------
23456                      Walgreen                 so-so              marketing
                                                                go to there              
---------------------------------------------------------------------------------------------------------
34567                      Go-Go                      improved       Eng
------------------------------------------------------------------------------------------------------------
0
 
arnoldCommented:
You have to use your asp/asp.net handle the formatting combining the description from the multiple rows or do you currently pass the data set directly into a formatting for output?
0
 
wasabi3689Author Commented:
I don't think the MS SQL data warehouse report is asp or asp.net application.
0
 
arnoldCommented:
0
 
wasabi3689Author Commented:
all good comments are very helpful
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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