Post reply

SQL help with Pivot

  • June 1, 2022 at 4:13 pm

    Go to Answer

    #4042706

    I am trying to use PIVOT to convert rows into columns.

    Following is the sample code with some data. I want to get one row for each VID, OrdDate

    However, the following code is not creating one row for each VID, OrdDate. It keeps them as is and I am looking for 2 records. Any help is greatly appreciated.

    CREATE TABLE #t1([VID] [varchar](100) NULL, [OrdDate] [datetime] NOT NULL, [QID] [varchar](50) NULL, [Response] [varchar](50) NULL, [ODate2] [datetime]) ON [PRIMARY]

    insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','0 and calm','2022-02-11 10:00:00.000')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','drowsy','2022-02-11 10:07:00.000')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','+2 agitated','2022-02-11 10:08:00.000')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','O2','95','2022-02-11 10:23:00.000')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','RA Score','-3 moderate sedation','2022-02-11 11:11:00.000')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','Res rate','16','2022-02-11 14:00:00.000')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','Res rate','32','2022-02-11 14:23:00.000')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','BD pressure','141/87','2022-02-11 15:00:00.000')

    insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','0 and calm','2022-02-11 10:00:00.000')
    insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','-1 drowsy','2022-02-11 10:07:00.000')
    insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','+2 agitated','2022-02-11 10:08:00.000')
    insert into #t1 values('22222','2022-01-10 13:37:06.000','O2','95','2022-02-11 10:23:00.000')
    insert into #t1 values('22222','2022-01-10 13:37:06.000','RA Score','-3 moderate sedation','2022-02-11 11:11:00.000')
    insert into #t1 values('22222','2022-01-10 13:37:06.000','Res rate','16','2022-02-11 14:00:00.000')
    insert into #t1 values('22222','2022-01-10 13:37:06.000','Res rate','32','2022-02-11 14:23:00.000')
    insert into #t1 values('22222','2022-01-10 13:37:06.000','BD pressure','141/87','2022-02-11 15:00:00.000')

    Select [VID], [OrdDate],
    [C Result],[RA Score],[PP score],[Temp],[Res rate],[O2],[BD pressure]
    from #t1
    PIVOT(MAX([Response])
    FOR [QID] in ([C Result],[RA Score],[PP score],[Temp],[Res rate],[O2],[BD pressure])) as P

    drop table #t1

  • June 1, 2022 at 4:42 pm

    #4042707

    Can you also post an image showing the results you would like to see, based on this data?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 1, 2022 at 5:05 pm

    #4042760

    Please see the attachment

     

    • This reply was modified 2 years, 5 months ago by  don075.

  • June 1, 2022 at 5:17 pm

    #4042762

    Capture

  • June 1, 2022 at 6:33 pm

    #4042800

    Try this:

    SELECT VID
          ,OrdDate
      ,CResult = MAX(IIF(QID = 'C Result', Response, NULL))
      ,RAScore = MAX(IIF(QID = 'RA Score', Response, NULL))
      ,PPScore = MAX(IIF(QID = 'PP Score', Response, NULL))
      ,Temp = MAX(IIF(QID = 'Temp', Response, NULL))
      ,ResRate = MAX(IIF(QID = 'Res rate', Response, NULL))
      ,O2 = MAX(IIF(QID = 'O2', Response, NULL))
      ,BDPressure = MAX(IIF(QID = 'BD pressure', Response, NULL))

    FROM #t1
    GROUP BY VID
            ,OrdDate;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 1, 2022 at 6:45 pm

    #4042803

    or  the one below - but output will never be the one you desire

    Select *
    from (select distinct VID
               , QID
               , OrdDate
               , Response from #t1) t1
    PIVOT(MAX([Response])
    FOR [QID] in ([C Result],[RA Score],[PP score],[Temp],[Res rate],[O2],[BD pressure])) as P
    order by vid

  • June 1, 2022 at 7:40 pm

    Answer

    #4042810

    I would think you'd want the last Response rather than just picking one seemingly at random (as in your results) or the value that happened to be the MAX one (in the other posters' code).

    ;WITH cte_get_last_values AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY VID, OrdDate, QID ORDER BY ODate2 DESC) AS row_num
        FROM #t1
    )
    SELECT 
        VID, OrdDate,
        MAX(CASE WHEN QID = 'C Result' THEN Response END) AS [C Result],
        MAX(CASE WHEN QID = 'RA Score' THEN Response END) AS [RA Score],
        MAX(CASE WHEN QID = 'PP Score' THEN Response END) AS [PP core],
        MAX(CASE WHEN QID = 'Temp' THEN Response END) AS [Temp],
        MAX(CASE WHEN QID = 'Res Rate' THEN Response END) AS [Res Rate],
        MAX(CASE WHEN QID = 'O2' THEN Response END) AS [O2],
        MAX(CASE WHEN QID = 'BD Pressure' THEN Response END) AS [BD Pressure]
    FROM cte_get_last_values
    WHERE row_num = 1
    GROUP BY VID, OrdDate
    ORDER BY VID, OrdDate

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • June 1, 2022 at 8:26 pm

    #4042839

    Great. Thanks for all the replies. Much appreciated.

  • June 11, 2022 at 9:43 am

    This was removed by the editor as SPAM

  • June 13, 2022 at 5:05 pm

    #4048880

    JohnnyCantrell wrote:

    Thanks for the suggestions.

    Hi and welcome aboard.

    Be advised that your post looks and smells like a precursor to spam.  If you're a spammer, please go away.  We're watching.

    If your not a spammer then, like I said, welcome aboard.  You'll find some interesting stuff here.

    --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)

  • June 27, 2022 at 5:22 am

    This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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