Post reply

How to show table in one row

  • June 7, 2022 at 11:47 am

    #4045559

    Hello everyone,

    I want to display the information in the table in one row for each status (show the max create date of status) like the example.

    I would like to know how to do that

    Tks, Gilad

    Attachments:
    You must be logged in to view attached files.

  • June 7, 2022 at 12:52 pm

    #4045614

    Ahoi,

    for future posts:

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

     

    for current post:

    The thing ur looking for is probably Pivot/Unpivot from SQL Server, theres examples of how to use it on google

    I want to be the very best
    Like no one ever was

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • June 7, 2022 at 1:23 pm

    #4045619

    You may want to take a look at the following 2 articles

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

    In the meantime, when posting a question, please help us to help you.  Give us readily consumable data

    CREATE TABLE #Data (
        ID         int      NOT NULL
      , userid     int      NOT NULL
      , CreateDate datetime NOT NULL
      , [status]   int      NOT NULL
    );

    INSERT INTO #Data ( ID, userid, CreateDate, [status] )
    VALUES ( 775205, 3297, '2022-06-07 10:08:44.010', 100 )
         , ( 775205, 3297, '2022-06-07 10:06:47.853', 100 )
         , ( 775205, 3297, '2022-06-07 10:06:26.290',  80 )
         , ( 775205, 3297, '2022-06-07 10:05:57.193', 300 )
         , ( 775205,   99, '2022-06-06 22:05:30.980',  10 );

     

     

    Then we can help with a solution

    WITH cteData AS (
      SELECT *, rn= ROW_NUMBER() OVER (PARTITION BY ID, [status] ORDER BY CreateDate DESC)
      FROM #Data
    )
    SELECT cte.ID
         ----------------------------------------------------------------------
         , status_10     =  MAX(CASE WHEN cte.[status] = 10 THEN cte.[status]   END)
         , userid_10     =  MAX(CASE WHEN cte.[status] = 10 THEN cte.userid     END)
         , createdate_10 =  MAX(CASE WHEN cte.[status] = 10 THEN cte.CreateDate END)
         ----------------------------------------------------------------------
         , status_80     =  MAX(CASE WHEN cte.[status] = 80 THEN cte.[status]   END)
         , userid_80     =  MAX(CASE WHEN cte.[status] = 80 THEN cte.userid     END)
         , createdate_80 =  MAX(CASE WHEN cte.[status] = 80 THEN cte.CreateDate END)
         ----------------------------------------------------------------------
         , status_100     = MAX(CASE WHEN cte.[status] = 100 THEN cte.[status]   END)
         , userid_100     = MAX(CASE WHEN cte.[status] = 100 THEN cte.userid     END)
         , createdate_100 = MAX(CASE WHEN cte.[status] = 100 THEN cte.CreateDate END)
         ----------------------------------------------------------------------
         , status_300     = MAX(CASE WHEN cte.[status] = 300 THEN cte.[status]   END)
         , userid_300     = MAX(CASE WHEN cte.[status] = 300 THEN cte.userid     END)
         , createdate_300 = MAX(CASE WHEN cte.[status] = 300 THEN cte.CreateDate END)
    FROM cteData AS cte
    WHERE cte.rn = 1
    GROUP BY cte.ID

    How to post data/code on a forum to get the best help.

  • June 7, 2022 at 2:43 pm

    #4045676

    Wow DesNorton Tks alot u help me so much!!!

Viewing 4 posts - 1 through 3 (of 3 total)

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