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
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.
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)
Oops, that should teach me to refresh a page before posting an answer. Sorry Adriaan, didn't noticed you've replied already!
[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
[quote user="subhash6008"]i wnat to show data in report (ssrs)[/quote] There must be some way as I specified in my last post
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.
