How to Concatenate Many Rows With Same Id in SQL

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



Leave a reply



Submit