Single customer row with annual term columns in SQL

Question

I am pulling customer numbers and renewal dates (policy term start dates) from a table in SQL and trying to generate a new table with a single row for each customer.

if i just pull customer numbers and the term effective dates, i get multiple rows for each customer for every unique term date: e.g.

-customer 1 5/12/2016
-customer 1 5/12/2017
-customer 2 8/27/2012
-customer 2 8/27/2013
-customer 2 8/27/2014
-customer 2 8/27/2015
-customer 2 8/27/2016
-customer 2 8/27/2017
-customer 3 3/25/2012
-customer 3 3/25/2013
-customer 3 3/25/2014
-customer 3 3/25/2015

I would like to automatically generate the data where each customer is on a single row with multiple columns representing the year of their respective terms. e.g.:

-customer 1                                                 5/12/2016       
-customer 2 8/27/2012   8/27/2013   8/27/2014   8/27/2015   8/27/2016       
-customer 3 3/25/2012   3/25/2013   3/25/2014   3/25/2015

My code in SQL looks like this:

SELECT
    CUSTOMER,
    case when Year(TERM_EFFECTIVE_DATE) = 2012 then TERM_EFFECTIVE_DATE end as "2012",
    case when Year(TERM_EFFECTIVE_DATE) = 2013 then TERM_EFFECTIVE_DATE end as "2013",

etc

But my output is giving me multiple rows for each customer and null values in the columns where the years did not match. e.g.:

-customer 1 N/A        N/A       N/A         N/A         5/12/2016
-customer 1 N/A        N/A       N/A         N/A         N/A
-customer 2 8/27/2012  N/A       N/A         N/A         N/A
-customer 2 N/A        8/27/2013 N/A         N/A         N/A
-customer 2 N/A        N/A       8/27/2014   N/A         N/A
-customer 2 N/A        N/A       N/A         8/27/2015   N/A
-customer 2 N/A        N/A       N/A         N/A         8/27/2016
-customer 2 N/A        N/A       N/A         N/A         N/A
-customer 3 3/25/2012  N/A       N/A         N/A         N/A
-customer 3 N/A        3/25/2013 N/A         N/A         N/A
-customer 3 N/A        N/A       3/25/2014   N/A         N/A
-customer 3 N/A        N/A       N/A         3/25/2015   N/A
-customer 3 N/A        N/A       N/A         N/A         3/25/2016

Is there a way i can fix this and reduce the rows to one per customer?

This seems to apply to your question. https://stackoverflow.com/a/15745076/4892466 — Troy Turley, Mar 15 '18 at 17:03

Juan Tarquino · Accepted Answer · 2018-03-15T17:14:01.900

0

One way of making one row is to have an outer query so that you group by customer the results you got in your last step :

    select customer, max("2012") "2012", max("2013") "2013" -- so on so on for every year
from (
 --your existing query
) details
group by customer

Option 2

Or, just do the Max and grouping in your original query:

    select customer, 
max(case when Year(TERM_EFFECTIVE_DATE) = 2012 then TERM_EFFECTIVE_DATE end) as "2012", 
max(case when Year(TERM_EFFECTIVE_DATE) = 2013 then TERM_EFFECTIVE_DATE end) "2013" -- so on so on for every year
        from 
    ...
        group by customer

edited Mar 15 '18 at 17:14

answered Mar 15 '18 at 17:08

Juan Tarquino

967
7
13

using the outer query worked. i had tried the max function in the original query and was still getting the multiple rows per customer. – pac Mar 15 '18 at 17:23

Single customer row with annual term columns in SQL

1 Answers1