Merge Two Rows in SQL

Merging multiple rows with same column in SQL Server

Use This

SELECT category,segment,payment = STUFF((
                                SELECT '+' + CONVERT(VARCHAR,payment)
                                FROM TABLENAME t
                                WHERE t.category = TABLENAME.category AND t.segment = TABLENAME.segment                               
                                FOR XML PATH('')
                                ), 1, 1, '')
FROM TABLENAME 
GROUP BY category,segment

if you get payment with + sign then use stuff like this.

SELECT category,segment,SUM(payment) as payment
FROM TABLENAME 
GROUP BY category,segment

if you want sum then remove stuff and use Sum(payment) only.

How to merge two rows and sum the columns

You are looking for the aggregate function SUM (which doesn't require a sub-query) e.g.

SELECT product, SUM(quantity*unit_price) AS Total_Price
FROM shopping_history
GROUP BY product

Combine two rows in a single row with the same ID name

A simple aggregation should do the trick

Example

Select ID
      ,ID1 = max(ID1)
      ,ID2 = max(ID2)
      ,ID3 = max(ID3)
 From  YourTable
 Group By ID

Merge two rows in sql and create new columns

To get the first / last rows' values with some specified ordering, use the window functions first_value / last_value

The window frame clause must be specified explicitly for LAST_VALUE to work since the default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which always yields the current row for LAST_VALUE.

DISTINCT drops all duplicate rows in the following query and returns only 1 row for each combination of field1, field2

SELECT DISTINCT
  field1
, field2
, FIRST_VALUE(entered_by) OVER w first_entered_by
, FIRST_VALUE(entered_date) OVER w first_entered_date
, LAST_VALUE(entered_by) OVER w last_entered_by
, LAST_VALUE(entered_date) OVER w last_entered_date
FROM mytable
WINDOW w AS (
  PARTITION by field1, field2
  ORDER BY entered_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY field1

Here's a DB Fiddle illustrating how to do it with sample data & set up code

How to merge two rows with one different field value into one row with concatenated field

In MySQL there is a function called GROUP_CONCAT(), this function in MySQL is used to concatenate data from multiple rows into one field.

This is an aggregate (GROUP BY) function which returns a String value if the group contains at least one non-NULL value. Otherwise, it returns NULL.

The syntax for this one is like this,

SELECT col1, col2, ..., colN
GROUP_CONCAT ( [DISTINCT] col_name1 
[ORDER BY clause]  [SEPARATOR str_val] ) 
FROM table_name GROUP BY col_name2;

col1, col2, ...colN: These are the column names of the table.

col_name1: Column of the table whose values are concatenated into a single field for each group.

table_name: Name of the table.

col_name2: Column of the table according to which grouping is done.


Use of various clauses inside GROUP_CONCAT() function,

1. Dinstinct: It eliminates the repetition of values from results.
2. Order By: It sorts the values of the group in a
   specific order and then concatenates them.
3. Separator: By default, the values of the group are separated by the (, ) operator. In order to change this separator value, a Separator clause is used followed by a string literal. It is given as Separator ‘str_value’.
   

Input:

++++++++++++++++++++++++++++++++++++++++++++++
id    fname    lname   dept_id   strength  `
++++++++++++++++++++++++++++++++++++++++++++++
1     Harry    Potter    2      Leadership
3      Hermione Granger  3      Hard-working
1      Harry    Potter   2      Responsible

Using simple GROUP_CONCAT() function:

SELECT emp_id, fname, lname, dept_id, 
GROUP_CONCAT ( strength ) as "strengths" 
FROM employee group by id;

Output:

++++++++++++++++++++++++++++++++++++++++++++++
id    fname    lname   dept_id   strength  
++++++++++++++++++++++++++++++++++++++++++++++
1     Harry    Potter    2      Leadership,Responsible
3     Hermione Granger   3      Hard-working

BQ - How to merge two rows with json value into one row with the two values

With help of string manipulation, following approach seems to be possible.

WITH table AS (
  SELECT 'S-1' sid, 'aaa' guid, 'CN=DESKTOP' dn, 'DESKTOP' name, 'DESKTOP' display_name UNION ALL
  SELECT 'S-1' sid, 'aaa' guid, 'CN=DESKTOP' dn, 'DESKTOP' name, 'DESKTOP' display_name UNION ALL
  SELECT 'S-2' sid, 'bbb' guid, 'CN=DESKTOP' dn, 'DESKTOP' name, 'DESKTOP' display_name UNION ALL
  SELECT 'S-2' sid, 'bbb' guid, 'CN=DESKTOP' dn, 'DESKTOP' name, 'DESKTOP' display_name 
)
SELECT sid, "{" || STRING_AGG(data) || "}" data FROM (
  SELECT sid, '"' || ROW_NUMBER() OVER (PARTITION BY sid) || '": ' || TO_JSON_STRING(t) data 
    FROM table t
)
GROUP BY sid;

output:

or, if each sid has a same and fixed sidcount, you can use pivot to generate similar result like below.

SELECT sid, TO_JSON_STRING(STRUCT(_1, _2)) data 
  FROM (
    SELECT sid, ROW_NUMBER() OVER (PARTITION BY sid) AS rn, t AS data 
      FROM table t
  ) PIVOT (ANY_VALUE(data) FOR rn IN (1, 2));

---

Related Topics