none
Flattening Table Data

    Question

  • I have data stored in a table with the following definition:

    CREATE TABLE [dbo].[837835_SvcLin_Adjts_Details]( [SLAdjRowID] [int] IDENTITY(1,1) NOT NULL, [RSvcLinID] [int] NOT NULL, [ClaimAdjGrpCode] [char](2) NOT NULL, [AdjRsnCode] [varchar](4) NOT NULL, [AdjAmt] [smallmoney] NOT NULL, [AdjQty] [int] NULL )

    Sample data:

    I need to be able to group this data by the RSvcLinID and ClaimAdjGrpCode, thereby flattening it.  For example, for RSvcLinID = 62643, which contains two 'PR' values for ClaimAdjGrpCode, I would need to rearrange the data into:

    RSvcLinID  ClaimAdjGrpCode   AdjRsnCode1  AdjAmt1   AdjQty1  AdjRsnCode2  AdjAmt2  AdjQty2 

    62643                PR                  1              2.60           NULL              2               9.12       NULL

    It is critical that the AdjRsnCodes keep their correspondence to the appropriate AdjAmt and AdjQty, and there could be up to six iterations of these flattened columns per row (ie AdjRsnCode(n), AdjAmt(n), AdjQty(n), where n = 1 -->6).

    Any help would be GREATLY appreciated!

                         


    Blankfiend




    • Edited by John Thay Friday, November 01, 2013 2:35 AM format
    Friday, November 01, 2013 2:31 AM

Answers

  • ;With cte As
    (Select RSvcLinID, ClaimAdjGrpCode, 
       AdjRsnCode, AdjAmt, AdjQty,
       Row_Number() Over(Partition By RSvcLinID, ClaimAdjGrpCode Order By SLAdjRowID) As rn
    From dbo.[837835_SvcLin_Adhts_Details])
    Select RSvcLinID, ClaimAdjGrpCode,
      Max(Case When rn = 1 Then AdjRsnCode End) As AdjRsnCode1,
      Max(Case When rn = 1 Then AdjAmt End) As AdjAmt1,
      Max(Case When rn = 1 Then AdjQty End) As AdjQty1,
      Max(Case When rn = 2 Then AdjRsnCode End) As AdjRsnCode2,
      Max(Case When rn = 2 Then AdjAmt End) As AdjAmt2,
      Max(Case When rn = 2 Then AdjQty End) As AdjQty2,
    ... thru
      Max(Case When rn = 6 Then AdjRsnCode End) As AdjRsnCode6,
      Max(Case When rn = 6 Then AdjAmt End) As AdjAmt6,
      Max(Case When rn = 6 Then AdjQty End) As AdjQty6
    From cte
    Group By RSvcLinID, ClaimAdjGrpCode
    Order By RSvcLinID, ClaimAdjGrpCode;
    

    Tom
    • Marked as answer by John Thay Friday, November 01, 2013 3:51 AM
    Friday, November 01, 2013 3:44 AM