Post reply

Multiple Rows of Data To Single Row With One ID

  • October 16, 2012 at 3:04 pm

    #283511

    Hi,

    I'm trying to alter a table to contain data in a horizontal (row) format rather than a vertical (column) format.

    The data I have is similar to:

    Acct_ID,Name,Start_Date,End_Date,Program,Program_ID

    1234,Joe Smith,1/1/2012,1/15/2012,Code1,5678

    1234,Joe Smith,2/1/2012,2/15/2012,Code2,5678

    1234,Joe Smith,1/5/2012,1/15/2012,Code3,5678

    9101,Bob Frank,2/1/2012,2/15/2012,Code1,1213

    9101,Bob Frank,3/1/2012,3/15/2012,Code2,1213

    9101,Bob Frank,4/1/2012,4/15/2012,Code3,1213

    9101,Bob Frank,5/1/2012,5/15/2012,Code4,1213 ...and so on

    I'd like for it to be:

    Acct_ID, Name, Start_Date1, End_Date1, Program1, Start_Date2, End_Date2, Program2, Start_Date3, End_Date3, Program3, Start_Date4...,Program_ID

    1234,Joe Smith, 1/1/2012, 1/15/2012, Code1, 2/1/2012, 2/15/2012, Code2...etc

    I've explored using Pivot and Case. All help is appreciated.

    Thanks!

  • October 16, 2012 at 3:37 pm

    #1549807

    Please see this similar question:

    http://ask.sqlservercentral.com/questions/94213/converting-multiple-rows-into-single-row.html

  • October 16, 2012 at 3:59 pm

    #1549812

    edgar58 (10/16/2012)

    I've explored using Pivot and Case. All help is appreciated.

    Thanks!

    Please see the following article. Change SUM to MAX to do text and dates, etc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 16, 2012 at 4:12 pm

    #1549819

    edgar58 (10/16/2012)

    Hi,

    I've explored using Pivot and Case. All help is appreciated.

    Thanks!

    Have you explored using Case (Cross Tabs) with ROW_NUMBER with PARTITION BY Acct_ID?

    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 16, 2012 at 11:42 pm

    #1549871

    Jeff Moden (10/16/2012)

    edgar58 (10/16/2012)

    I've explored using Pivot and Case. All help is appreciated.

    Thanks!

    Please see the following article. Change SUM to MAX to do text and dates, etc.

    I think you may have forgotten to include the links:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • October 17, 2012 at 7:56 am

    #1550012

    dwain.c (10/16/2012)

    Jeff Moden (10/16/2012)

    edgar58 (10/16/2012)

    I've explored using Pivot and Case. All help is appreciated.

    Thanks!

    Please see the following article. Change SUM to MAX to do text and dates, etc.

    I think you may have forgotten to include the links:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    Definitely a NEC problem on my part. Thanks for the cover, Dwain.

    NEC = Not Enough Coffee

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 17, 2012 at 11:57 am

    #1550167

    Edgar, i had a similar situation and here in the code i used. obviously you'll sub in your details 😀

    DECLARE @tColors TABLE

    (rownum INT IDENTITY (1,1) primary key,

    CustomerID int,

    Color varchar(50)

    )

    declare

    @rownum int = 1,

    @maxrow int = 0,

    @ColorList varchar(2000) = null,

    @CustomerID int = 26

    BEGIN

    INSERT INTO @tColors

    (CustomerID, Color)

    SELECT distinct co.CustomerID, co.Color

    from Testing.Colors co

    WHERE CustomerID = @CustomerID

    ORDER BY co.CustomerID, co.Color DESC

    SELECT @maxrow = count(1) from @tColors

    WHILE @rownum <= @maxrow

    BEGIN

    set @ColorList = CASE WHEN @ColorList IS NULL THEN '' ELSE @ColorList + ', ' end + (SELECT color from @tColors where rownum = @rownum)

    set @rownum = @rownum + 1

    END

    SELECT @ColorList as CustomersColors

    END

    Dana

    "Drats! Foiled again!"

  • October 18, 2012 at 9:58 am

    #1550583

    Thank you all for the information. I ended up using Case. However, I have some instances where one ID has the same program but for different date spans.

    Select P.ID,P.SSN,P.MCD_ID,P.Lname,P.Fname,P.Admit,P.Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID,

    MAX(Case WHEN P.EligibleDesc = 'Program' Then P.Eligible END) As 'Eligible_PRG',

    MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligibleType END) As 'EligibleType_PRG',

    MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligibleDesc END) As 'Elig_PRG',

    MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligBeginDate END) As 'EligBegin_PRG',

    MAX(Case WHEN P.EligibleDesc = 'Program' Then P.EligEndDate END) As 'EligEnd_PRG',

    From Eligibility_Final P

    group by P.Patacctnum,P.SSN,P.MCD_ID_Instate,P.Lname,P.Fname,P.Admit,P.Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID

    This produces semi-desired results; however, when some records have the same program but different date spans only the MAX dates are selected and the other records are eliminated. If I remove the MAX(Dates) then I get repeating rows. Can someone help me out with this issue?

    The results I want would be:

    ID,SSN,MCD_ID,Lname,Fname,Admit,Disch,RCP_Fname,RCP_Lname,RCPDOB,RcpMedicaidID,Eligible_PRG,EligibleType_PRG,Elig_PRG,EligBegin_PRG,EligEnd_PRG,Eligible_PRG,EligibleType_PRG,Elig_PRG,EligBegin_PRG,EligEnd_PRG

    Where the information in bold is the data for the first date span and the information in italics is the data for the second date span and so on.

    Thanks!!

Viewing 8 posts - 1 through 7 (of 7 total)

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