Manipulate a SQL recordset row with ROW_NUMBER,CTE and CASE

Question: How can I write a query to return a recordset and do something to a specific row? I understand this is a presentation issue - because the obvious answer is to return the recordset and then do some manipulation at the presentation layer, but the current situation requires me o run the query at the sqlcmd line and then create an output file.

For example:

The main column to focus is on the GeoExposure column , although at a relational level 5000 would appear on every row , I only want 5000 to appear just on the FIRST row.

Policy_ID Import_Date Policy_name GeoExposure
123 2018-08-10 10:44:51.290 My_Policy 1 5000
123 2018-08-09 10:44:51.290 My_Policy 1 -

 How can I write the query ?

Answer: It is possible to complete this task by using a combination of ROW_NUMBER() , CASE and CTE. You could also the row by row  method of CURSOR , but I prefer to not to use the CURSOR method , unless it's essential.

Here is an example  with some sample data of how you could join two tables and then do something to the first row. I'm applying a row number to every row within the CTE.  Then when I do the SELECT  I can use a CASE statement to identify row number 1 and do some things. 

create table #main_table
(Policy_ID INT,
Policy_name varchar(100),
GeoExposure INT)

create table #child_table
(Policy_ID INT , --foreign key to #main_table Policy_ID
Import_Date datetime)

INSERT INTO #main_table 
SELECT 123, 'My_Policy 1', 5000

INSERT INTO #child_table
SELECT 123,getdate()
UNION
SELECT 123,getdate() - 1; 


--Method 1 using Common Table Expression
WITH MyCTE AS(
select ROW_NUMBER() OVER(ORDER BY Import_Date DESC) AS Row,
t1.Policy_ID,
t1.Import_Date,
t2.Policy_name,
convert(varchar(20),t2.GeoExposure) as GeoExposure
from #child_table t1
inner join #main_table t2 ON t1.Policy_ID = t2.Policy_ID
)
SELECT Policy_ID,
Import_Date,
Policy_name,
GeoExposure = 
CASE WHEN row = 1 THEN GeoExposure
ELSE '-'
END
 FROM MyCTE

 
drop table #main_table
drop table #child_table

 Read more on sqlcmd , row_number, CTE

JOIN IN SQL FAQ

SQL Server CTE and PARTITION BY (SQL Server DBA)

SQL Server - SQLCMD with screen output and output file (SQL ...