SQL Server Performance

query

Discussion in 'SQL Server Log Shipping' started by subhash6008, Jun 27, 2009.

  1. subhash6008 New Member

    HI


    actually i am having data in one table is as shiow below

    sno marks1 mark2 marks3

    1 98 99 90
    1 98 99 90
    1 98 99 90
    2 98 97 90
    2 98 97 90



    I WANT OUT PUT AS SHOWN BELOW


    sno marks1 mark2 marks3

    1 98 99 90
    1 0 0 0
    1 0 0 0
    2 98 97 90
    2 0 0 0



    i want query for this out put

    Thanks

    Subhash
  2. FrankKalis Moderator

    Welcome to the forum!
    Is this a homework question?
  3. subhash6008 New Member

    it is a probem which i am really facing please send query as eary as possiabe
    Thanks
    subhash
  4. Adriaan New Member

    Well, there are reporting tools (like MS Access) that let you "hide duplicates" on successive rows on a report.
    Other than that, if there is no unique identifier on the row, then you can only use a cursor that copies the rows over into a temp table, replacing duplicate values with zeroes.
    If you do have a unique identifier then a UNION might work for you. But if you need to process a large number of rows then the cursor option is often faster.
  5. FrankKalis Moderator

    In that case, may I say that your real problem seems to be the design of your table?
    On SQL Server 2005 or SQL Server 2008 you could do something like this:
    DECLARE @t TABLE (sno int, marks1 int, marks2 int, marks3 int)
    INSERT INTO @t
    SELECT 1, 98, 99, 90
    UNION ALL SELECT 1, 98, 99, 90
    UNION ALL SELECT 1, 98, 99, 90
    UNION ALL SELECT 2, 98, 97, 90
    UNION ALL SELECT 2, 98, 97, 90


    ;WITH mycte
    AS
    (SELECT
    ROW_NUMBER () OVER (PARTITION BY sno, marks1, marks2, marks3 ORDER BY sno) AS rn
    ,sno,
    marks1,
    marks2,
    marks3
    FROM
    @t)

    SELECT
    sno,
    CASE WHEN rn = 1 THEN marks1 ELSE 0 END AS marks1,
    CASE WHEN rn = 1 THEN marks2 ELSE 0 END AS marks2,
    CASE WHEN rn = 1 THEN marks3 ELSE 0 END AS marks3
    FROM
    mycte

    1 98 99 90
    1 0 0 0
    1 0 0 0
    2 98 97 90
    2 0 0 0

    (5 row(s) affected)
    However, since you've posted in the SQL Server 2000 forum, probably the best choice here would be to use a cursor. Maybe something like this:
    CREATE TABLE #t (sno int, marks1 int, marks2 int, marks3 int);
    DECLARE @sno int, @marks1 int, @marks2 int, @marks3 int, @counter int;

    DECLARE mycursor CURSOR FORWARD_ONLY FOR SELECT * FROM @t ORDER BY sno, marks1, marks2, marks3;
    OPEN mycursor;
    FETCH NEXT FROM mycursor INTO @sno, @marks1, @marks2, @marks3;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF NOT EXISTS (SELECT 1
    FROM
    #t
    WHERE
    sno = @sno AND
    marks1 = @marks1 AND
    marks2 = @marks2 AND
    marks3 = @marks3)
    INSERT INTO #t SELECT @sno, @marks1, @marks2, @marks3;
    ELSE
    INSERT INTO #t SELECT @sno, 0, 0, 0;
    FETCH NEXT FROM mycursor INTO @sno, @marks1, @marks2, @marks3;
    END
    CLOSE mycursor;
    DEALLOCATE mycursor;

    SELECT
    *
    FROM
    #t;

    DROP TABLE #t;
    1 98 99 90
    1 0 0 0
    1 0 0 0
    2 98 97 90
    2 0 0 0

    (5 row(s) affected)

  6. FrankKalis Moderator

    Oops, that should teach me to refresh a page before posting an answer. Sorry Adriaan, didn't noticed you've replied already!
  7. Madhivanan Moderator

    [quote user="subhash6008"]HI


    actually i am having data in one table is as shiow below

    sno marks1 mark2 marks3

    1 98 99 90
    1 98 99 90
    1 98 99 90
    2 98 97 90
    2 98 97 90



    I WANT OUT PUT AS SHOWN BELOW


    sno marks1 mark2 marks3

    1 98 99 90
    1 0 0 0
    1 0 0 0
    2 98 97 90
    2 0 0 0



    i want query for this out put

    Thanks

    Subhash
    [/quote]
    Where do you want to show data?
    As Adriaan pointed, this is the presentation issue. You can make use of Reporting tool's Suppress if duplicated feature with small modification
  8. subhash6008 New Member

    i wnat to show data in report (ssrs)
  9. Madhivanan Moderator

    [quote user="subhash6008"]i wnat to show data in report (ssrs)[/quote]
    There must be some way as I specified in my last post
  10. Adriaan New Member

    As Madhivanan pointed out, there is a "Suppress if duplicated" feature in the reporting tool. I don't know that tool, but I assume you will find this feature on the Properties for the fields where you want to display the columns marks1, mark2 and marks3. (It will probably show blanks instead of zeroes, but that should not be a big concern.)
    If you want to do it the hard way, why not check Frank's suggestion.

Share This Page