How to concatenate many rows with same id in sql?
In SQL-Server you can do it in the following:
QUERY
SELECT id, displayname =
STUFF((SELECT DISTINCT ', ' + displayname
FROM #t b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, '')
FROM #t a
GROUP BY id
TEST DATA
create table #t
(
id int,
displayname nvarchar(max)
)
insert into #t values
(1 ,'Editor')
,(1 ,'Reviewer')
,(7 ,'EIC')
,(7 ,'Editor')
,(7 ,'Reviewer')
,(7 ,'Editor')
,(19,'EIC')
,(19,'Editor')
,(19,'Reviewer')
OUTPUT
id displayname
1 Editor, Reviewer
7 Editor, EIC, Reviewer
19 Editor, EIC, Reviewer
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 multiple rows (with same ID) into one?
I believe this will work (I tested it on an Oracle 11g, not a Teradata):
SELECT *
FROM (SELECT id_nbr AS ID,
contact_type AS contype,
contact_first_name AS firstName,
contact_last_name AS lastName,
contact_phone_number AS phoneNumber,
contact_address AS address,
contact_email AS email
FROM database.account_info
WHERE contact_type in ('AAA', 'BBB', 'CCC')
)
PIVOT (max(firstName) AS firstName,
max(lastName) AS lastName,
max(phoneNumber) AS phone,
max(email) AS email,
max(address) AS address
FOR contype IN ('AAA', 'BBB', 'CCC')
)
The last line may require an alias, one of:
) AS derived_pivot
) derived_pivot
Same ID with multiple records to single row
If you just want to view the output suggested in table B using table A, then use a pivot query:
SELECT
source,
ID,
1 AS Type_1_ID
MAX(CASE WHEN Type_ID = 1 THEN Error_info END) AS Error_info_1,
2 AS Type_2_ID,
MAX(CASE WHEN Type_ID = 2 THEN Error_info END) AS Error_info_2
FROM yourTable
GROUP BY
source,
ID;
Related Topics
How to in a Query Calculate the Sum of the Union
Select Different Values from Same Column in a Table and Display It Under Different Columns
How to Display Employee Names Starting With a and Then B in SQL
Localhost/Phpmyadmin Giving Page Not Found Error
How to Add Leading Zero When Number Is Less Than 10
Select All Rows With the Same Value in Column 1 But Different Values in Columns 2 and 3 Using SQL
Mysql - Get All Records That Have More Than 1 Record for the Same Id
How to Combine First Name, Middle Name and Last Name in SQL Server
Multiple Rows to One Comma-Separated Value Different Id
Sql Get Parent Where Children Have Specific Values
How to Modify Query to Remove Double-Quotes from All Columns
How to Store Multiple Values in Single Field in SQL Database
Oracle Sql, Concatenate Multiple Columns + Add Text
Insert Multiple Rows Without Repeating the "Insert into ..." Part of the Statement
Select Rows Where Every Child Row Meets a Condition
Postgresql Delete Multiple Rows from Multiple Tables