i want to get all field but without same name in sql-CodePudding

Hi I want to get all field for example

select * from Stock where MemberId=3430

without same name value but I need Id

select distinct name,id,MemberId where MemberId=3430

Id is unique so distinct is not working correctly for me

Result is like

Id Name
1, Stock1
2, Stock2
3, Stock2
4, Stock1

It doesn't work because id is unique

CodePudding user response：

You didn't specify the flavor of SQL that you're using. I've produced an answer for you that works in Postgres, SQL Server, and MySQL.

To be clear, my understanding of what you're looking for is the ability to return 1 record for "Stock2" with both of the id's associated with that value, rather than 2 separate records. To do this you need some sort of string aggregation capability. Here are three examples that will produce results you are looking for:

Postgres - dbfiddle link - https://www.db-fiddle.com/f/cWoFG13QYyi52Vww5HxoDi/1

SELECT array_agg(id), name
from sample
group by name;

SELECT array_to_string(array_agg(id), ','), name
from sample
group by name

MySQL

SELECT group_concat(id), name
from sample
group by name

SQL Server - sqlfiddle link - http://sqlfiddle.com/#!18/111ba7/4/0

SELECT string_agg(id, ','), name
from sample
group by name;