CODEWITHSUNDEEP
sql
froncthetonc
froncthetonc

Reputation: 5

records with same account ID with multiple values as one table with each value in a separate field

I have a table that looks like this
Table: AcctPrograms

    Acct     ProgramOptions   Date_End
    100      A                2016-09-01 00:00:00.0000000   
    100      B                2017-01-01 00:00:00.0000000
    101      B                2017-01-01 00:00:00.0000000
    101      C                2018-01-01 00:00:00.0000000
    101      D                2017-01-01 00:00:00.0000000
    102      C                2018-01-01 00:00:00.0000000
    103      F                2016-06-01 00:00:00.0000000
    103      G                2015-12-14 00:00:00.0000000
    ...

I want to create a view vw_AcctProgramOptions that looks like this:

    Acct   ProgramOption1  ProgramOption2  ... ProgramOption10
    100    A               B
    101    B               C              
    102    C
    103    F               G
    ...

The catch is that an acct in the AcctPrograms table can have MORE than 10 options but I'm only interested in collecting the first 10 order by MaxDate(Date_End) from the AcctPrograms table.

Any help would be greatly appreciated. I've been working on this for a couple of days and can't seem to get it working.

Thanks so much in advance.

EDIT: Using MSSQL 2008

Upvotes: 0

Views: 56

Answers (2)

ying lam
ying lam

Reputation: 1

--Vashi's answer with a the A a little bit lower at the query

--try the query

create table #table
(
    Acct int,
    ProgramOptions varchar(1),
    Date_End datetime
)

insert into #table
(
    Acct,
    ProgramOptions,
    Date_End
)
values
(    100,      'A',                '2016-09-01 00:00:00'   ),
(    100,      'B',                '2017-01-01 00:00:00'   ),
(    101,      'B',                '2017-01-01 00:00:00'   ),
(    101,      'C',                '2018-01-01 00:00:00'   ),
(    101,      'D',                '2017-01-01 00:00:00'   ),
(    102,      'C',                '2018-01-01 00:00:00'   ),
(    103,      'F',                '2016-06-01 00:00:00'   ),
(    103,      'G',                '2015-12-14 00:00:00'   )


SELECT A.ACCT,
MAX(CASE WHEN A.RNUM = 1 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION1,
MAX(CASE WHEN A.RNUM = 2 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION2,
MAX(CASE WHEN A.RNUM = 3 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION3,
MAX(CASE WHEN A.RNUM = 4 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION4,
MAX(CASE WHEN A.RNUM = 5 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION5,
MAX(CASE WHEN A.RNUM = 6 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION6,
MAX(CASE WHEN A.RNUM = 7 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION7,
MAX(CASE WHEN A.RNUM = 8 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION8,
MAX(CASE WHEN A.RNUM = 9 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION9,
MAX(CASE WHEN A.RNUM = 10 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION10
FROM
(SELECT
ACCT, PROGRAMOPTIONS, DATE_END, ROW_NUMBER() OVER (PARTITION BY ACCT ORDER BY DATE_END) AS RNUM
FROM
#table
) A
GROUP BY A.ACCT;


drop table #table

Upvotes: 0

Vash
Vash

Reputation: 1787

Use ROW_NUMBER to order ProgramOptions grouped by Acct and pull the first 10 in the order in the form of columns by using CASE WHEN statements. 

SELECT A.ACCT,
MAX(CASE WHEN A.RNUM = 1 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION1,
MAX(CASE WHEN A.RNUM = 2 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION2,
MAX(CASE WHEN A.RNUM = 3 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION3,
MAX(CASE WHEN A.RNUM = 4 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION4,
MAX(CASE WHEN A.RNUM = 5 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION5,
MAX(CASE WHEN A.RNUM = 6 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION6,
MAX(CASE WHEN A.RNUM = 7 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION7,
MAX(CASE WHEN A.RNUM = 8 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION8,
MAX(CASE WHEN A.RNUM = 9 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION9,
MAX(CASE WHEN A.RNUM = 10 THEN A.PROGRAMOPTIONS ELSE NULL END) AS PROGRAMOPTION10
FROM
(SELECT
ACCT, PROGRAMOPTIONS, DATE_END, ROW_NUMBER() OVER (PARTITION BY ACCT ORDER BY DATE_END) AS RNUM
FROM
ACCTPROGRAMS A
)
GROUP BY A.ACCT;

Upvotes: 1

Related Questions