define Person and Seller

RPA

SOLVED CLOSED ACTIVE ANSWERED
User: RPA
Posted: on Apr 26, 2016 07:25 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 2841

Hi! I used this below code it’s worked.

SELECT * FROM FruitTable
SELECT * FROM SecondTable
 
DECLARE @PersonId INT
SET @PersonId = 2
 
DECLARE @FruitIds VARCHAR(50)
SELECT  @FruitIds = FruitId FROM SecondTable WHERE Id = @PersonId
 
SELECT  @PersonId as 'Id',STUFF((SELECT ',' + Fruit FROM
                (
                    SELECT  CONVERT(VARCHAR,@PersonId) as PersonId,* FROM FruitTable WHERE Id IN (SELECT CAST(item AS INTEGER)
                                                                                            FROM dbo.SplitString(@FruitIds, ','))
                ) as childTable
WHERE  mainTable.PersonId= childTable.PersonId
FOR XML PATH('')), 1, 1, '') AS Fruitname,
REPLACE(SUM(CAST(REPLACE(Price,',','.') AS DECIMAL(18,2))),'.',',') AS 'Summa price'           
FROM
(
    SELECT CONVERT(VARCHAR,@PersonId) as PersonId,* FROM FruitTable WHERE Id IN (SELECT CAST(item AS INTEGER)
                                                                           FROM dbo.SplitString(@FruitIds, ','))
) as mainTable
GROUP BY mainTable.PersonId

Here I add two table PersoneTable and SellerTable

PersoneTable

Id	Name	Adress
1	Nurullo	District Shohmansur
2	Sadriddin	District Firdavsi

SellerTable

Id	Name
1	Firuz
2	Asror

Secondtable

Id	FruitId	PersonId	SellerId
1	1,3,5	2	1

In result I want output this:

Id	Fruit	Person	Address	Seller	Summa Price
1	Apple, Melon, Grapes	Sadriddin	District Firdavsi	Firuz	8,5

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on Apr 26, 2016 07:27 AM
Report

Hi,

Please refer below Code.

SQL

SELECT * FROM FruitTable
SELECT * FROM SecondTable
SELECT * FROM PersoneTable
SELECT * FROM SellerTable
 
DECLARE @PersonId INT
SET @PersonId = 1
 

SELECT PersonId,STUFF((SELECT ',' + Fruit FROM
(
    SELECT st.Id as PersonId,fr.*
	FROM FruitTable fr,SecondTable st
	WHERE fr.Id IN (SELECT CAST(item AS INTEGER) FROM dbo.SplitString(st.FruitId, ','))
	AND st.Id = @PersonId
) as childTable
WHERE  mainTable.PersonId= childTable.PersonId
FOR XML PATH('')), 1, 1, '') AS Fruitname,
mainTable.Person,
[Address],
[Seller],
REPLACE(SUM(CAST(REPLACE(Price,',','.') AS DECIMAL(18,2))),'.',',') AS 'Summa price'
FROM
(
    SELECT st.Id as PersonId,fr.*,pt.Name Person,pt.[Address],slt.Name Seller
	FROM FruitTable fr,SecondTable st,PersoneTable pt,SellerTable slt
	WHERE fr.Id IN (SELECT CAST(item AS INTEGER) FROM dbo.SplitString(st.FruitId, ','))
	AND st.Id = @PersonId
	AND st.SellerId = slt.Id
	AND st.PersonId = pt.Id
) as mainTable
GROUP BY mainTable.PersonId,mainTable.Person,mainTable.[Address],mainTable.Seller

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

define Person and Seller

Company

Explore

Follow us

Contact Us