1

My SQL Query Results,

EmployeeID    EmployeeName    Department
1             John            Sales
2             Robert          Sales
3             Sudhir          Sales
4             Roj             Development

Output Should be

EmployeeID    EmployeeName    Department
1             John            Sales
2             Robert          
3             Sudhir          
4             Roj             Development
Amar Mishra
  • 89
  • 2
  • 10

1 Answers1

1

You can do this:

;WITH CTE
AS
( 
   SELECT EmployeeID, EmployeeName, Department,
     ROW_NUMBER() OVER(PARTITION BY Department ORDER BY(SELECT 1)) rownum
  FROM table1
)
SELECT
  EmployeeId,
  EmployeeName,
  CASE rownum
    WHEN 1 THEN Department
    ELSE ''
  END AS Department
FROM CTE
ORDER BY EmployeeID;

SQL Fiddle Demo

Note that: Try not to do these sort of formatting stuff in SQL. SQL is not about formatting.

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Yes, It helped. I used two columns: DENSE_RANK() OVER(ORDER BY(SELECT Department)) AS 'A', ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Department) AS 'B' And Then used following in select statement: case when convert(varchar(5),[B])='1' then convert(varchar(5),[A]) else '' end 'NO.' – Amar Mishra Dec 06 '12 at 13:14
  • @Mahmoud Gamal, Does this command run on MYSQL? I tried to run it but it has errors. – kiLLua Jan 19 '21 at 02:07
  • @kiLLua - No it won't run. Mysql doesn't have row number function like SQL Server, but there are other workaround to achieve the same. But, for this issue, I don't recommend do any formatting for the data in the sql side, formatting should be done in the front end not in SQL. So, if you are trying to do the same, do it in the front end not in sql. Just select your data normally and then in the front end side it will be very easy to format it the way you want. I hope this helps. – Mahmoud Gamal Jan 19 '21 at 09:54