Sign in
 

none
Flattening Table Data

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote 
    ;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
    |