Conrad Addo February 2016

Include query result on separate line using SELECT CASE or similar

I would like to include my queries on separate lines using SELECT CASE. Instead of having Field, Tape Capture Value and Tape Value repeated on the same row, it would be great to have it on a separate line. Could someone please help with this? Thanks if you can!

Here is my current query:

SELECT T.[Loan Identifier], TC.Reviewer, 

    'Valuation Date' AS [Field],
    CASE 
    WHEN TC.[Valuation Date] <> T.[Valuation Date] THEN TC.[Valuation Date] END AS [Tape Capture Value], T.[Valuation Date] AS [Tape Value],

    'Underwriter name' AS [Field],
    CASE 
    WHEN TC.[Underwriter name] <> T.[Underwriter name] THEN TC.[Underwriter name] END AS [Tape Capture Value], T.[Valuation Date] AS [Tape Value]

FROM   [dbo].[Tape] AS T 
   LEFT JOIN [dbo].[Tape Capture] AS TC 
          ON T.[Loan Identifier] = TC.[Loan Identifier] 
TC.Reviewer IS NOT NULL AND TC.[Primary_review_complete?] = 1

Instead of having my result in its current form:

Original

I would like it in the below form:

Required

Answers


Gordon Linoff February 2016

The simplest method is probably outer apply:

SELECT T.[Loan Identifier], TC.Reviewer, v.*
FROM [dbo].[Tape] T LEFT JOIN
     [dbo].[Tape Capture] TC 
     ON T.[Loan Identifier] = TC.[Loan Identifier]  OUTER APPLY
     (VALUES ('Valuation Date', CASE WHEN TC.[Valuation Date] <> T.[Valuation Date] THEN TC.[Valuation Date] END, NULL)
             ('Underwriter name', NULL, CASE WHEN TC.[Underwriter name] <> T.[Underwriter name] THEN TC.[Underwriter name] END)
     ) as v(field, [Tape Capture Value], [Tape Value])
WHERE TC.Reviewer IS NOT NULL AND TC.[Primary_review_complete?] = 1

Oops. Instead of the table value constructor, use select with union all:

SELECT T.[Loan Identifier], TC.[Underwriter name], v.*
FROM [dbo].[Tape] T LEFT JOIN
     [dbo].[Tape Capture] TC 
     ON T.[Loan Identifier] = TC.[Loan Identifier]  OUTER APPLY
     (SELECT 'Valuation Date', CASE WHEN TC.[Valuation Date] <> T.[Valuation Date] THEN TC.[Valuation Date] END, NULL
      UNION ALL
      SELECT 'Underwriter name', NULL, CASE WHEN TC.[Underwriter name] <> T.[Underwriter name] THEN TC.[Underwriter name] END
     ) as v([field], [Tape Capture Value], [Tape Value])

The logic is demonstrated in the SQL Fiddle, but it mah not be exaclty what you want.


Kamran Farzami February 2016

You can use UNION ALL and do the following:

**

SELECT T.[Loan Identifier], TC.Reviewer, 

    'Valuation Date' AS [Field],
    CASE 
    WHEN TC.[Valuation Date] <> T.[Valuation Date] THEN TC.[Valuation Date] END AS [Tape Capture Value], T.[Valuation Date] AS [Tape Value] 

FROM   [dbo].[Tape] AS T 
   LEFT JOIN [dbo].[Tape Capture] AS TC 
          ON T.[Loan Identifier] = TC.[Loan Identifier] 
TC.Reviewer IS NOT NULL AND TC.[Primary_review_complete?] = 1
UNION ALL
SELECT T.[Loan Identifier], TC.Reviewer, 

    'Underwriter name' AS [Field],
    CASE 
    WHEN TC.[Underwriter name] <> T.[Underwriter name] THEN TC.[Underwriter name] END AS [Tape Capture Value], T.[Valuation Date] AS [Tape Value]

FROM   [dbo].[Tape] AS T 
   LEFT JOIN [dbo].[Tape Capture] AS TC 
          ON T.[Loan Identifier] = TC.[Loan Identifier] 
TC.Reviewer IS NOT NULL AND TC.[Primary_review_complete?] = 1

**

Post Status

Asked in February 2016
Viewed 2,952 times
Voted 5
Answered 2 times

Search




Leave an answer