SQL Server multiple row value seperated by comma in sql database

ppcbkaq5 于 2023-05-05 发布在其他

关注(0)|答案(3)|浏览(144)

i have an e commerce site and a single ID can have a multiple product orders. As of now i have this:

as you can see, it shows redudancy on my table. I want to have an output like:

Carbon Dixode, Industrial Oxygen

since they are on the same ID (10). same goes with

Compressed Air, Kerosene, Medical Oxygen

here is my code behind:

private void GetOrderList()
    {
        ShoppingCart k = new ShoppingCart()
        {
            Flag = 0
        };
        DataTable dt = k.GetOrderList();

        gvCustomerOrders.DataSource = dt;
        gvCustomerOrders.DataBind();
        gvCustomerOrders.HeaderRow.TableSection = TableRowSection.TableHeader;
    }

this is the GetOrderList()

internal DataTable GetOrderList()
    {
        SqlParameter[] parameters = new SqlParameter[1];
        parameters[0] = DataLayer.DataAccess.AddParameter("@Flag", Flag, System.Data.SqlDbType.Int, 20);
        DataTable dt = DataLayer.DataAccess.ExecuteDTByProcedure("SP_GetOrderList2", parameters);
        return dt;
    }

and here is the stored procedure:

ALTER procedure [dbo].[SP_GetOrderList2]
(
@Flag int
)
AS
BEGIN
    BEGIN TRY

    if(@Flag <>0)
    Begin
        Select *
        FROM CustomerDetails where Id=@Flag;

    End
    else
    begin
        select p.[name],cd.Id, cd.CustomerName, cd.CustomerEmailID,cd.CustomerPhoneNo,cd.CustomerAddress,cd.TotalPrice,cd.OrderDateTime, cd.PaymentMethod FROM CustomerDetails cd Inner Join CustomerProducts cp ON cp.CustomerID = cd.Id Inner Join Products p ON cp.ProductID = p.ProductID
    end

    END TRY

    BEGIN CATCH

        PRINT('Error Occured')
    END CATCH

    END

what i really want here is to avoid redudancy in my table. i want the products on the same ID stays together since my price are already combined. Any help will be greatly appreciated.

sql-server

来源：https://stackoverflow.com/questions/37949648/multiple-row-value-seperated-by-comma-in-sql-database

3条答案

按热度按时间

mwkjh3gx1#

You can use group_concat() function for this purpose. Try this sample query.

SELECT id, name, phoneno, email, group_concat(product), price FROM <TABLE_NAME> GROUP BY phoneno

Thank You.

赞(0）回复(0）举报 2023-05-05

zsohkypk2#

Ok.

You can put new code in your If secttion..

----your code
if(@Flag <>0)
    Begin
       declare @Product VARCHAR(Max)
       SELECT  @Product = COALESCE(@Product + ', ', '') + Product 
       FROM CustomerDetails where Id=@Flag;
        ----select your entire field except product column.  In product columns you have only select `---@Product`  
     select distinct id, name, phoneno, email ,@Product  product
      from CustomerDetails where Id=@Flag;

    End
-----your code

In @Product variable your product column data will be concatenate by comma. according to id .

If You also want it in else section then you have to follow my previous answer..

赞(0）回复(0）举报 2023-05-05

zqry0prt3#

1>DECLARE Table Variable

declare @Table Table(RecID int identity,Id int,Product nvarchar(50))

2>Insert it unique value..

insert into @Table
select distinct id,Product from CustomerDetails

3> Declare some variable

declare @i int,@cnt int,@Id int
declare @Product VARCHAR(Max) 
select @cnt=count(*),@i=1 from @Table
while @i<=@cnt
begin
select @Id=id from @Table where RecID =@i
set @Product =''
SELECT  @Product = COALESCE(@Product + ',', '') + Product 
FROM CustomerDetails where id=@id
update @Table set Product =@Product where id=@id
set @i=@i+1
end

4>Select ur table with table variable..

select distinct d.id,d.name,d.phoneno,d.email,t.product
from CustomerDetails d
join @Table t on t.id=d.id
.

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server multiple row value seperated by comma in sql database

3条答案

相关问题

热门标签

最新问答