Post reply

Pivoting

  • October 8, 2015 at 1:57 am

    #308609

    I have attached three scripts

    Input Scripts

    1. dbo.InputTest1.Table

    2. dbo.inputtest2.Table

    Output Which i want is available in third script dbo.outputtest1.Table

    SO i need help to write a select query to get the output mentioned in script dbo.outputtest1.Table by using two input scripts.

    Below columns mentioned in the output script Should be generated dynamically based on the data available in the input scripts.

    [CreditRating|Moody] [nvarchar](255) NULL,

    [CreditRating|SP] [nvarchar](255) NULL,

    [CreditRating|Fitch] [nvarchar](255) NULL,

    [RatingsAsOf|ModdysDate] [datetime] NULL,

    [RatingsAsOf|SPDate] [datetime] NULL,

    [RatingsAsOf|FitchDate] [datetime] NULL,

  • October 8, 2015 at 7:05 am

    #1832270

    Please what you tried so far?

    Here is my guess you need combine two tables into one first

    with allRatings as (

    select * from [dbo].[InputTest1]

    union all

    select *, null as [FormOrderForInstnRating],null as [SortOrder]

    from [dbo].[InputTest2]

    )

    select * from allRatings -- the table to pivot

    ?

  • October 8, 2015 at 9:32 am

    #1832336

    I have no idea on the rules to get to the output. Where does the FormOrder comes from? Are the 2 input tables related? or are they just 2 set of rows? What are you pivoting? How do you group them?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 9, 2015 at 1:58 am

    #1832454

    I have mentioned detailed explanation about the requirement

    1. AS i mentioned previously we have to build the output result(Outputtest1) set based on inputtest1 and inputtest2

    2. inputtest1 this result set contains latest records infomation

    3. inputtest2 this result set contains history records information records infomation

    in this result set we have to get recent recent 5 records based on condition

    ROW_NUMBER() OVER(PARTITION BY ShortestName , CreditRatingTranche ORDER BY RatingsAsOf DESC) AS RNo

    4. Below colummns should be build on logic

    ISNULL(CreditRating,'')+ISNULL('('+ RatingsWatchActionAbbrev+')' ,'')+'

    '+ISNULL(CreditRatingDirection,'')+'</br>' CreditRatingFormatted

    CreditRating|Moody

    CreditRating|SP

    CreditRating|Fitch

    Below columns are build based on RatingsAsOf column

    RatingsAsOf|ModdysDate

    RatingsAsOf|SPDate

    RatingsAsOf|FitchDate

    Columns ( Moody / SP / Fitch ) should be generated dynamically based on the data avilability

    CreditRating ( Moody / SP / Fitch )

    RatingsAsOf ( Moody / SP / Fitch )

    Columns should be pivoted as mentioned in the Outputtest1 table

    5. Generate FormOrder

    If you sort the formOrder in the Outputtest1 table you can able to understand the data pattern how this sequesnce is generated

    6.Finally Sort Order of the Outputtest1 table should be same as the CreditRatingTranche column in the inputtest1 table

    the final output resultset and order should be matched Outputtest1 table

  • October 9, 2015 at 2:07 am

    #1832456

    May be as simple as the query below. Or may be not. 🙂

    with allRatings as (

    select * from [dbo].[InputTest1]

    union all

    select *

    ,null as [FormOrderForInstnRating]

    ,null as [SortOrder]

    from [dbo].[InputTest2]

    ), nbr as (

    select *

    ,rn = row_number() over(partition by [ShortestName],[CreditRatingTranche] order by [RatingsAsOf] desc)

    from allRatings

    )

    select [CreditRatingTranche]

    ,[FormOrder] = max([FormOrderForInstnRating])

    ,[RatingsAsOf|FitchDate] = max(case [ShortestName] when 'Fitch Ratings' then [RatingsAsOf] end)

    ,[CreditRating|Fitch] = max(case [ShortestName] when 'Fitch Ratings'

    then [CreditRating] + '

    ' + [CreditRatingDirection] +'</br>'end)

    --...

    from nbr

    where rn <= 6

    group by [CreditRatingTranche],rn

    order by [CreditRatingTranche],rn

    ;

  • October 9, 2015 at 2:54 am

    #1832478

    ramrajan (10/9/2015)

    I have mentioned detailed explanation about the requirement

    Try

    with allRatings as (

    select * from [dbo].[InputTest1]

    union all

    select *

    ,[FormOrderForInstnRating] =

    (select top(1) FormOrderForInstnRating from [dbo].[InputTest1] t1

    where t2.[CreditRatingTranche] =t1.[CreditRatingTranche])

    ,[SortOrder] = 1+row_number() over(partition by [ShortestName],[CreditRatingTranche] order by [RatingsAsOf] desc)

    from [dbo].[InputTest2] t2

    )

    select [CreditRatingTranche]

    ,[FormOrder] = row_number() over(order by

    case sortOrder when 1 then 1 else 2 end

    , [CreditRatingTranche]

    , max([RatingsAsOf]) desc)

    ,[RatingsAsOf|FitchDate] = max(case [ShortestName] when 'Fitch Ratings' then [RatingsAsOf] end)

    ,[CreditRating|Fitch] = max(case [ShortestName] when 'Fitch Ratings'

    then ISNULL(CreditRating,'')+ISNULL('('+ RatingsWatchActionAbbrev+')' ,'')+'<br>'+ISNULL(CreditRatingDirection,'')+'</br>' end)

    --...

    from allRatings

    where sortOrder <= 6

    group by [CreditRatingTranche],sortOrder

    order by max(FormOrderForInstnRating),sortOrder

    ;

    Ahh, site engine doesn't like <br> tag

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

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