-1

I need SQL code for below issue

I have two table as shown below

Id  |Name
1   |Roy
1   |Roy
1   |Roy
1   |Roy
1   |Roy



Name    Type
Roy |    GE
Roy |    RA
Roy |    SA
Roy |    PA
Roy |    PA

These two tables have primary key Name.

Desired output

Id  Type
1 | GE,RA,SA,PA
James Z
  • 12,209
  • 10
  • 24
  • 44
Sri Sri
  • 3
  • 2

4 Answers4

0

You'll have to carry out two steps.

  1. Join the two tables on name, select id and type. Your SQL will look something like:

    select id, type from table1, table2 where table1.name = table2.name

  2. Use one of the solutions provided in this excellent previous answer to collapse the duplicated column into a comma separated column depending on what your database is.

Community
  • 1
  • 1
Alterlife
  • 6,557
  • 7
  • 36
  • 49
0

The below code doesn't work unless the ID is unique. Please ignore this answer as explained in the comments.

select PersonName.Name, STRING_AGG(NameType.Type, ',') as 'Type' from PersonName
    left outer join NameType
    on PersonName.Name = NameType.Name
    Group by PersonName.Name

Explanation: 1. Consider the first table as PersonName 2. Second table as NameType 3. Join the two tables on Name field/column 4. Use STRING_AGG to Concatenate 5. As it is an Aggregate function, you will need a group by Statement

Raja
  • 56
  • 9
  • 1
    Hi @Raja, your example is for SQLServer I believe. Well as you can see here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=a965ee6175882039fcc5bf53317399fc your query does not work. 1. It returns name and not Id as OP asked for. 2. It also returns multiple values for type. Hope this will help... Cheers! – VBoka Nov 14 '19 at 17:50
  • 1
    Thank you. My bad. I thought the first table id column was a Primary key. – Raja Nov 14 '19 at 17:59
  • Hi Raja, Thanks for your response .. But i am getting error as "SQL Error [9829] [S0001]: STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation." – Sri Sri Nov 15 '19 at 09:51
  • Hello @SriSri, so none of my examples helped ? I have sent you DEMO's where you can see how it works for every database specified and with your data! What seems to be problem there ? With my query's you get exactly you asked for... – VBoka Nov 15 '19 at 10:58
0

If you are using Oracle:

SELECT A.ID, LISTAGG(B."type", ',') WITHIN GROUP (ORDER BY B.NAME) "TYPE"
FROM 
(SELECT T1.NAME, T1.ID
FROM T1
GROUP BY T1.NAME, T1.ID) A
JOIN 
(SELECT T2.NAME, T2."type"
FROM T2
GROUP BY T2.NAME, T2."type") B
ON A.NAME = B.NAME
GROUP BY A.ID;

Here is the DEMO for Oracle.

If you are using SQLServer:

SELECT A.ID, STRING_AGG (B."type", ',') WITHIN GROUP (ORDER BY B.NAME) "TYPE"
FROM 
(SELECT T1.NAME, T1.ID
FROM T1
GROUP BY T1.NAME, T1.ID) A
JOIN 
(SELECT T2.NAME, T2."type"
FROM T2
GROUP BY T2.NAME, T2."type") B
ON A.NAME = B.NAME
GROUP BY A.ID;

Here is the DEMO for SQLServer.

And if you are using MySQL then:

SELECT A.ID, GROUP_CONCAT(B.type ORDER BY B.NAME separator ',')
FROM 
(SELECT t1.NAME, t1.ID
FROM t1
GROUP BY t1.NAME, t1.ID) A
JOIN 
(SELECT t2.NAME, t2.`type`
FROM t2
GROUP BY t2.NAME, t2.`type`) B
ON A.NAME = B.NAME
GROUP BY A.ID;

Here is the DEMO for MySQL

VBoka
  • 8,995
  • 3
  • 16
  • 24
0

Try this code for SQL:

select t1.Id, STRING_AGG(t2.Type, ',') as 'Type' from 
(select distinct ID, Person.PersonName from Person) t1
left outer join NameType t2
on t1.PersonName = t2.PersonName
Group by t1.Id

enter image description here

Raja
  • 56
  • 9
  • @Raha, I believe this solution will not remove the double type 'PA' that the OP has in his data. Check the demo: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=0478f2e92bf2d04c19238370fc182577 – VBoka Nov 15 '19 at 07:49
  • Thanks for your response . But i m getting the error as "SQL Error [9829] [S0001]: STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation." – Sri Sri Nov 15 '19 at 09:55