;With CTE
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY RowID ORDER BY Col DESC) AS Rn,Val,Col,RowID
FROM table t
UNPIVOT(Val FOR Col IN ([Col1],[Col2],...[Col9]))u
)

SELECT RowID,COALESCE(Val,0)
FROM (SELECT DISTINCT RowID FROM CTE)c
OUTER APPLY (SELECT Top 1 Val
             FROM CTE
             WHERE Val >0
             AND RowID = c.RowID
             ORDER BY Rn)c1 

CREATE FUNCTION dbo.fn_get_value
(
  @row_id    INT, 
  @col_name  VARCHAR(50)
)
RETURNS DEC(5,2)
AS
BEGIN
  DECLARE @value DEC(5,2);

  With CTE
  AS
  (
    SELECT RowID, Col, Val
    FROM table
    UNPIVOT(Val FOR Col IN (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9)) u
  )
  SELECT @value = Val FROM CTE C1
   WHERE RowID = @row_id
     AND Col = COALESCE((SELECT MAX(Col) FROM CTE
                          WHERE Val > 0.
                            AND RowID = C1.RowID
                            AND Col <= @col_name), 'Col1');
  RETURN (@value);
END
GO

;With CTE
AS
(
SELECT Val,Col,RowID
FROM table t
UNPIVOT(Val FOR Col IN ([Col1],[Col2],...[Col9]))u
)

SELECT RowID,COALESCE(Val,0)
FROM (SELECT DISTINCT RowID FROM CTE)c
OUTER APPLY (SELECT Top 1 Val
             FROM CTE
             WHERE Val >0
             AND RowID = c.RowID
             ORDER BY Col DESC)c1 

quote:

---

Originally posted by smh

Hi everyone.

I am looking at all of these, trying to understand, and will soon try them out on my work problem at the beginning of the week. I thank you all. This is very complicated for me, even though simply and elegantly put in the code examples. I have never used the coalsce or the pivot, unpivot, or rownumber() partitian. In this case I am using 9 if statements in the select statment on each row so I hope to your examples lead to a better way. By the way , the field names in my case are price1, price2, price3...price9

---