locked
Transposing repeated rows of datetime into columns in SQL 17 RRS feed

  • Question

  • I am working with a dataset of inpatient data in SQL version 17. The fields contain anonymized ID, admission data, discharge date <g class="gr_ gr_13 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="13" id="13">and</g> other relevant fields. Since some of the patients are readmitted, the ID field is not unique. The table looks like this: ID AdmDate1 DischDate
    10001 2012-10-16 2012-10-26 10001 2014-06-15 2014-06-18 10001 2014-12-21 2014-12-29 10002 2013-02-14 2013-02-20 10003 2013-01-23 2013-01-31 10004 2012-11-15 2012-11-19 10004 2014-09-26 2014-10-06 10005 2014-12-12 2014-12-23 10006 2013-10-23 2013-10-28 Since I want to calculate readmission rate and intervals between readmissions, I want to <g class="gr_ gr_11 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="11" id="11">creat</g> a table like this: ID AdmDate1 AdmDate2 AdmDate3 DischDate1 DischDate2 DischDate3
    10001 2012-10-16 2014-06-15 2014-12-21 10002 2013-02-14 None None Could anyone help me? Thanks in advance.

    Friday, November 9, 2018 3:10 PM

Answers

  • you can use classic crosstab based logic for this

    like

    SELECT ID,
    MAX(CASE WHEN Seq = 1 THEN AdmDate1 END) AS AdmDate1,
    MAX(CASE WHEN Seq = 2 THEN AdmDate1 END) AS AdmDate2,
    MAX(CASE WHEN Seq = 3 THEN AdmDate1 END) AS AdmDate3,
    MAX(CASE WHEN Seq = 1 THEN DischDate END) AS DischDate1,
    MAX(CASE WHEN Seq = 2 THEN DischDate END) AS DischDate2,
    MAX(CASE WHEN Seq = 3 THEN DischDate END) AS DischDate3
    FROM
    (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AdmDate1) AS Seq
    FROM InpatientDataTable
    )t
    GROUP BY ID

    If you want to make it dynamic based on  the number of available admissions use dynamic sql over above logic like

    DECLARE @MaxCount int,@PvtCols
    
    SELECT @MaxCount = MAX(AdmnCount)
    FROM
    (
    SELECT ID,COUNT(*) AS AdmnCount
    FROM InpatientDataTable
    GROUP BY ID
    )t
    
    ;With Numbers(N)
    AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1
    FROM Numbers
    WHERE N + 1 <= @MaxCount
    )
    
    SET @PvtCols = STUFF(SELECT ',MAX(CASE WHEN Seq = ' + CAST(N AS varchar(10)) + ' THEN AdmDate1 END) AS [AdmDate' + CAST(N AS varchar(10)) + '],MAX(CASE WHEN Seq = ' + CAST(N AS varchar(10)) + ' THEN DischDate END) AS [DischDate'  + CAST(N AS varchar(10)) + ']
    FROM Numbers
    ORDER BY N
    FOR XML PATH('')),1,1,'')
    
    DECLARE @SQL varchar(max) = 'SELECT ID,' + @PvtCols + ' FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AdmDate1) AS Seq FROM InpatientData)t GROUP BY ID'
    
    EXEC(@SQL)
    
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by MelakuH Sunday, November 11, 2018 1:32 PM
    Saturday, November 10, 2018 7:36 AM

All replies

  • Please fix your post, it's not readable. Additional see POSTING TIPS - Code, Images, Hyperlinks, Details

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, November 10, 2018 5:52 AM
  • you can use classic crosstab based logic for this

    like

    SELECT ID,
    MAX(CASE WHEN Seq = 1 THEN AdmDate1 END) AS AdmDate1,
    MAX(CASE WHEN Seq = 2 THEN AdmDate1 END) AS AdmDate2,
    MAX(CASE WHEN Seq = 3 THEN AdmDate1 END) AS AdmDate3,
    MAX(CASE WHEN Seq = 1 THEN DischDate END) AS DischDate1,
    MAX(CASE WHEN Seq = 2 THEN DischDate END) AS DischDate2,
    MAX(CASE WHEN Seq = 3 THEN DischDate END) AS DischDate3
    FROM
    (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AdmDate1) AS Seq
    FROM InpatientDataTable
    )t
    GROUP BY ID

    If you want to make it dynamic based on  the number of available admissions use dynamic sql over above logic like

    DECLARE @MaxCount int,@PvtCols
    
    SELECT @MaxCount = MAX(AdmnCount)
    FROM
    (
    SELECT ID,COUNT(*) AS AdmnCount
    FROM InpatientDataTable
    GROUP BY ID
    )t
    
    ;With Numbers(N)
    AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1
    FROM Numbers
    WHERE N + 1 <= @MaxCount
    )
    
    SET @PvtCols = STUFF(SELECT ',MAX(CASE WHEN Seq = ' + CAST(N AS varchar(10)) + ' THEN AdmDate1 END) AS [AdmDate' + CAST(N AS varchar(10)) + '],MAX(CASE WHEN Seq = ' + CAST(N AS varchar(10)) + ' THEN DischDate END) AS [DischDate'  + CAST(N AS varchar(10)) + ']
    FROM Numbers
    ORDER BY N
    FOR XML PATH('')),1,1,'')
    
    DECLARE @SQL varchar(max) = 'SELECT ID,' + @PvtCols + ' FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AdmDate1) AS Seq FROM InpatientData)t GROUP BY ID'
    
    EXEC(@SQL)
    
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by MelakuH Sunday, November 11, 2018 1:32 PM
    Saturday, November 10, 2018 7:36 AM
  • Thank you very much. It solved the problem.
    Sunday, November 11, 2018 1:33 PM