Get the multiple rows data into single row.

Question

Get the multiple rows data into single row.

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
April 23, 2019 at 1:07 pm

#3524214
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
```
keyinstnAbbreviationRatingSymbolCreditWatchOutlookRatingDebtTypeDescriptionratingtypedescRatingSymbol_LCCreditWatchOutlook_LCRatingDebtTypeDescription_LCratingtypedesc_LC
4000193International Business Machines CorporationBBBStableIssuer Credit RatingForeign Currency LTNULLNULLNULLNULL
4000193International Business Machines CorporationAAStableIssuer Credit RatingLocal Currency LTNULLNULLNULLNULL
```
---Expected Data:
```
keyinstnAbbreviationRatingSymbolCreditWatchOutlookRatingDebtTypeDescriptionratingtypedescRatingSymbol_LCCreditWatchOutlook_LCRatingDebtTypeDescription_LCratingtypedesc_LC
4000193International Business Machines CorporationBBBStableIssuer Credit RatingForeign Currency LTAAStableIssuer Credit RatingLocal Currency LT
```
Advance thanks for your help.

Thanks
Bhanu

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

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.
Larnu.uk

rVadim Hall of Fame Points: 3969 More actions · Answer 2

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

--Vadim R.

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 3

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: 4035 More actions · Answer 4

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'

This reply was modified 5 years ago by DataAnalyst011.

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 5

kbhanu15

SSCarpal Tunnel

Points: 4493

April 24, 2019 at 5:29 am

#3528454

Thank you so much. It is working fine.