Get the multiple rows data into single row.

  • kbhanu15

    SSCarpal Tunnel

    Points: 4396

    Hi Team,

     

    Please help me to get the expected data for the below details.

    My present data looks like below.

    create table #test

    (

    keyinstn INT,

    Abbreviation varchar(100),

    RatingSymbol varchar(100),

    CreditWatchOutlook varchar(100),

    RatingDebtTypeDescription varchar(100),

    ratingtypedesc varchar(100),

    RatingSymbol_LC varchar(100),

    CreditWatchOutlook_LC varchar(100),

    RatingDebtTypeDescription_LC varchar(100),

    ratingtypedesc_LC varchar(100)

    )

    insert into #test(keyinstn,Abbreviation,RatingSymbol,CreditWatchOutlook,RatingDebtTypeDescription,ratingtypedesc)

    select 4000193,'International Business Machines Corporation','BBB','Stable','Issuer Credit Rating','Foreign Currency LT'

    union all

    select 4000193,'International Business Machines Corporation','AA','Stable','Issuer Credit Rating','Local Currency LT'

    --Existing Data

    select * from #test

    keyinstn	Abbreviation	RatingSymbol	CreditWatchOutlook	RatingDebtTypeDescription	ratingtypedesc	RatingSymbol_LC	CreditWatchOutlook_LC	RatingDebtTypeDescription_LC	ratingtypedesc_LC
    4000193 International Business Machines Corporation BBB Stable Issuer Credit Rating Foreign Currency LT NULL NULL NULL NULL
    4000193 International Business Machines Corporation AA Stable Issuer Credit Rating Local Currency LT NULL NULL NULL NULL

     

    ---Expected Data:

    keyinstn	Abbreviation	RatingSymbol	CreditWatchOutlook	RatingDebtTypeDescription	ratingtypedesc	RatingSymbol_LC	CreditWatchOutlook_LC	RatingDebtTypeDescription_LC	ratingtypedesc_LC
    4000193 International Business Machines Corporation BBB Stable Issuer Credit Rating Foreign Currency LT AA Stable Issuer Credit Rating Local Currency LT

    Advance thanks for your help.

     

    Thanks

    Bhanu

  • Thom A

    SSC Guru

    Points: 98029

    Unfortunately SSC, and the use of tabs, has made your expected results an unreable mess. Guessing you need a conditional aggregation?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • rVadim

    Hall of Fame

    Points: 3803

    Assuming that division is on Foreign vs. Local currency, something like this:

    SELECT 
    keyinstn,
    Abbreviation,

    RatingSymbol = MAX(CASE WHEN ratingtypedesc LIKE 'Foreign%'
    THEN RatingSymbol END),
    CreditWatchOutlook = MAX(CASE WHEN ratingtypedesc LIKE 'Foreign%'
    THEN CreditWatchOutlook END),
    RatingDebtTypeDescription = MAX(CASE WHEN ratingtypedesc LIKE 'Foreign%'
    THEN RatingDebtTypeDescription END),
    ratingtypedesc = MAX(CASE WHEN ratingtypedesc LIKE 'Foreign%'
    THEN ratingtypedesc END),
    RatingSymbol_LC = MAX(CASE WHEN ratingtypedesc LIKE 'Local%'
    THEN RatingSymbol END),
    CreditWatchOutlook_LC = MAX(CASE WHEN ratingtypedesc LIKE 'Local%'
    THEN CreditWatchOutlook END),
    RatingDebtTypeDescription_LC = MAX(CASE WHEN ratingtypedesc LIKE 'Local%'
    THEN RatingDebtTypeDescription END),
    ratingtypedesc_LC = MAX(CASE WHEN ratingtypedesc LIKE 'Local%'
    THEN ratingtypedesc END)
    FROM #test
    GROUP BY keyinstn, Abbreviation

  • DataAnalyst011

    SSCarpal Tunnel

    Points: 4033

    Is the field keyinstn really the same value for both rows? I wasn't sure if that was actually intended to be a primary key (at least in concept), which would mean in the real dataset the numbers would need to be unique.

  • DataAnalyst011

    SSCarpal Tunnel

    Points: 4033

    UPDATE: Just saw rVadim's reply. That solution has a better execution plan from my tests.

    It that field does actually contain the same value, here is another idea:

    with cte as (
    select *
    from #test
    where ratingtypedesc = 'Foreign Currency LT'
    )

    select cte.keyinstn, cte.Abbreviation, cte.RatingSymbol, cte.CreditWatchOutlook, cte.RatingDebtTypeDescription, cte.ratingtypedesc, t.RatingSymbol as RatingSymbol_LC, t.CreditWatchOutlook as CreditWatchOutlook_LC, t.RatingDebtTypeDescription as RatingDebtTypeDescription, t.ratingtypedesc as ratingtypedesc_LC
    from cte
    join #test t on cte.keyinstn = t.keyinstn
    where 1=1
    and t.ratingtypedesc = 'Local Currency LT'
  • kbhanu15

    SSCarpal Tunnel

    Points: 4396

    Thank you so much. It is working fine.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply