Manipulate a SQL recordset row with ROW_NUMBER,CTE and CASE

10 August,2018 by Jack Vamvas

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 ...

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Manipulate a SQL recordset row with ROW_NUMBER,CTE and CASE


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer