how to remove duplicate record while paging in SQL Server

nedash

SOLVED CLOSED ACTIVE ANSWERED
User: nedash
Posted: on Oct 24, 2016 02:42 AM
Forum: SQL Server
Answer: 2
Views: 4572

this is Order_Users table in database I bind gridview from this table:

PeygiriCode	Post type	name	id
123	1	neda	1
123	1	neda	2
123	1	neda	3
450	2	sara	4

as you see in above table id 1,2,3 has same peygiricode

below is storeprocedure:

USE [Digimaster]
GO
/****** Object:  StoredProcedure [dbo].[NewOrder_Information]    Script Date: 10/22/2016 21:45:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewOrder_Information]
		@PageIndex INT = 1
        ,@PageSize INT = 5
        ,@RecordCount INT OUTPUT 
 
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [date] asc
      )AS RowNumber
     ,id
     ,PeygiriCode
     ,Quantity
     ,Tell 
     ,Name
     ,PostType
     ,[date] 
     ,[state]+' - '+city+'-'+' منطقه:'+zone as address
      INTO #Results
      FROM Order_Users 
      SELECT @RecordCount = COUNT(*)
      FROM #Results       
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1    
      DROP TABLE #Results
END

and:
 
  private void GetDocumentInfo(int pageIndex)
    {

        using (SqlConnection conn = General.GetConnection())
        {
            using (SqlCommand cmd = General.GetCommand("NewOrder_Information", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                cmd.Parameters.AddWithValue("@PageSize", PageSize);

                cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                conn.Open();
                SqlDataReader idr = cmd.ExecuteReader();
                if (idr.HasRows)
                {
                    GridView2.DataSource = idr;
                    GridView2.DataBind();
               
                }
                else
                {
                    GridView2.DataSource = idr;
                    GridView2.DataBind();
                }
                idr.Close();
                this.PopulatePagerP(Convert.ToInt32(cmd.Parameters["@RecordCount"].Value), pageIndex);

            }
        }
    }

I bind gridview from this table so according above SP it will show all 4 rows from table in gridview but I want it show id 1,2,3(with same peygiricode) in one row in gridview like:

PeygiriCode	Post type	name	id
123	1	neda	1
450	2	sara	4

how I should change SP to show row with same peygiricode in one row of gridview?

Best regards

Neda

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

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Oct 24, 2016 03:15 AM
Modified: on Feb 23, 2018 03:48 AM
Report

Hi nedash,

Refer the below sql sample.

ALTER PROCEDURE [dbo].[NewOrder_Information]
    @PageIndex INT = 1
    ,@PageSize INT = 5
    ,@RecordCount INT OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Test AS TABLE(PeygiriCode INT,PostType INT,NAME VARCHAR(50),ID INT IDENTITY)
    INSERT INTO @Test VALUES(123,1,'neda')
    INSERT INTO @Test VALUES(123,1,'neda')
    INSERT INTO @Test VALUES(123,1,'neda')
    INSERT INTO @Test VALUES(450,2,'sara1')
    INSERT INTO @Test VALUES(451,1,'sara2')
    INSERT INTO @Test VALUES(452,1,'sara3')
    INSERT INTO @Test VALUES(453,1,'sara4')
    INSERT INTO @Test VALUES(454,2,'sara5')
    INSERT INTO @Test VALUES(455,1,'sara6')
    INSERT INTO @Test VALUES(456,1,'sara7')
    INSERT INTO @Test VALUES(457,1,'sara8')
    INSERT INTO @Test VALUES(458,2,'sara9')
    INSERT INTO @Test VALUES(459,1,'sara10')
    INSERT INTO @Test VALUES(460,1,'sara11')
    INSERT INTO @Test VALUES(461,1,'sara12')
    INSERT INTO @Test VALUES(462,2,'sara13')
 
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0) ASC)AS RowNumber,PeygiriCode,PostType,NAME,id
    INTO #Results
    FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PeygiriCode ORDER BY id ASC) RNo,PeygiriCode,PostType,NAME,id FROM @Test)a
    WHERE RNo = 1
 
    SELECT @RecordCount = COUNT(*) FROM #Results

    SELECT PeygiriCode,PostType,NAME,id FROM #Results
    WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
 
    DROP TABLE #Results
END
 
EXEC [dbo].[NewOrder_Information] 1,5,5
EXEC [dbo].[NewOrder_Information] 2,5,5
EXEC [dbo].[NewOrder_Information] 3,5,5

Screenshot

dharmend...

0 4

Reply
ANSWER
User: dharmendr
Replied: on Oct 24, 2016 06:36 AM
Report

nedash says:
ALTER PROCEDURE [dbo].[NewOrder_Information]

               @PageIndex INT = 1

       ,@PageSize INT = 5

       ,@RecordCount INT OUTPUT

AS

BEGIN

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0) ASC)AS RowNumber,PeygiriCode,PostType,NAME,id,Quantity,Tell,address,date

   INTO #Results

   FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PeygiriCode ORDER BY id ASC) RNo,PeygiriCode,PostType,NAME,id,Quantity,Tell,address,date FROM Order_Users)a

   WHERE RNo = 1

   SELECT PeygiriCode,PostType,NAME,id,Quantity,Tell,Address,date FROM #Results

   WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

END

Change with the below query

ALTER PROCEDURE [dbo].[NewOrder_Information]
	@PageIndex INT = 1
	,@PageSize INT = 5
	,@RecordCount INT OUTPUT
AS
BEGIN
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0) ASC)AS RowNumber,PeygiriCode,PostType,NAME,id,Quantity,Tell,address,date
	INTO #Results
	FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PeygiriCode ORDER BY id ASC) RNo,PeygiriCode,PostType,NAME,id,Quantity,Tell,address,date FROM Order_Users)a
	WHERE RNo = 1

	SELECT @RecordCount = COUNT(*) FROM #Results

	SELECT PeygiriCode,PostType,NAME,id,Quantity,Tell,Address,date FROM #Results
	WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
END

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

how to remove duplicate record while paging in SQL Server

Company

Explore

Follow us

Contact Us