Update flags to values

  • I've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?

    Here's some set-up script:

    IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
      DROP TABLE #tbl;

    CREATE TABLE #tbl
    (
      Id INT PRIMARY KEY
    , C1 INT
    , C2 INT
    , C3 INT
    , C4 INT
    , C5 INT
    , C6 INT
    );

    INSERT #tbl
    (
      Id
    , C1
    , C2
    , C3
    , C4
    , C5
    , C6
    )
    VALUES
    (
      1, 0, 1, 0, 1, 0, 0
    )
    ,(
      2, 0, 0, 0, 0, 0, 0
    )
    ,(
      3, 0, 1, 0, 1, 1, 1
    );

    SELECT *
    FROM #tbl t;

    I want to run an UPDATE query on this table, such that it ends up looking like this:

    SELECT *
    FROM
       (
        VALUES
         (1, 2, 4, NULL, NULL, NULL, NULL)
        ,    (2, NULL, NULL, NULL, NULL, NULL, NULL)
        ,    (3, 2, 4, 5, 6, NULL, NULL)
       ) Results (Id, C1, C2, C3, C4, C5, C6);[/code]

    Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin - Thursday, January 26, 2017 12:05 PM

    I've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?

    Here's some set-up script:

    IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
      DROP TABLE #tbl;

    CREATE TABLE #tbl
    (
      Id INT PRIMARY KEY
    , C1 INT
    , C2 INT
    , C3 INT
    , C4 INT
    , C5 INT
    , C6 INT
    );

    INSERT #tbl
    (
      Id
    , C1
    , C2
    , C3
    , C4
    , C5
    , C6
    )
    VALUES
    (
      1, 0, 1, 0, 1, 0, 0
    )
    ,(
      2, 0, 0, 0, 0, 0, 0
    )
    ,(
      3, 0, 1, 0, 1, 1, 1
    );

    SELECT *
    FROM #tbl t;

    I want to run an UPDATE query on this table, such that it ends up looking like this:

    SELECT *
    FROM
       (
        VALUES
         (1, 2, 4, NULL, NULL, NULL, NULL)
        ,    (2, NULL, NULL, NULL, NULL, NULL, NULL)
        ,    (3, 2, 4, 5, 6, NULL, NULL)
       ) Results (Id, C1, C2, C3, C4, C5, C6);[/code]

    Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.


    UPDATE t #tbl
    SET Ci=a.C1
       , C2= a.C2
       , C3 = a.C3
       , C4 = a.C4
       , C5 = a.C5
       , C6 = a.C6
    FROM (SELECT *
    FROM
       (
        VALUES
         (1, 2, 4, NULL, NULL, NULL, NULL)
        ,    (2, NULL, NULL, NULL, NULL, NULL, NULL)
        ,    (3, 2, 4, 5, 6, NULL, NULL)
       ) Results (Id, C1, C2, C3, C4, C5, C6)) a
    JOIN #tbl t ON t.ID=a.id; 

  • Joe Torre - Thursday, January 26, 2017 12:17 PM

    Phil Parkin - Thursday, January 26, 2017 12:05 PM

    I've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?

    Here's some set-up script:

    IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
      DROP TABLE #tbl;

    CREATE TABLE #tbl
    (
      Id INT PRIMARY KEY
    , C1 INT
    , C2 INT
    , C3 INT
    , C4 INT
    , C5 INT
    , C6 INT
    );

    INSERT #tbl
    (
      Id
    , C1
    , C2
    , C3
    , C4
    , C5
    , C6
    )
    VALUES
    (
      1, 0, 1, 0, 1, 0, 0
    )
    ,(
      2, 0, 0, 0, 0, 0, 0
    )
    ,(
      3, 0, 1, 0, 1, 1, 1
    );

    SELECT *
    FROM #tbl t;

    I want to run an UPDATE query on this table, such that it ends up looking like this:

    SELECT *
    FROM
       (
        VALUES
         (1, 2, 4, NULL, NULL, NULL, NULL)
        ,    (2, NULL, NULL, NULL, NULL, NULL, NULL)
        ,    (3, 2, 4, 5, 6, NULL, NULL)
       ) Results (Id, C1, C2, C3, C4, C5, C6);[/code]

    Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.


    UPDATE t
    SET Ci=a.C1
       , C2= a.C2
       , C3 = a.C3
       , C4 = a.C4
       , C5 = a.C5
       , C6 = a.C6
    FROM (SELECT *
    FROM
       (
        VALUES
         (1, 2, 4, NULL, NULL, NULL, NULL)
        ,    (2, NULL, NULL, NULL, NULL, NULL, NULL)
        ,    (3, 2, 4, 5, 6, NULL, NULL)
       ) Results (Id, C1, C2, C3, C4, C5, C6)) a
    JOIN #tbl t ON t.ID=a.id; 

  • Joe Torre - Thursday, January 26, 2017 12:19 PM

    Joe Torre - Thursday, January 26, 2017 12:17 PM

    Phil Parkin - Thursday, January 26, 2017 12:05 PM

    I've been off sick for a few days and it seems to have taken a toll on my problem-solving skills. Who's up to the challenge?

    Here's some set-up script:

    IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
      DROP TABLE #tbl;

    CREATE TABLE #tbl
    (
      Id INT PRIMARY KEY
    , C1 INT
    , C2 INT
    , C3 INT
    , C4 INT
    , C5 INT
    , C6 INT
    );

    INSERT #tbl
    (
      Id
    , C1
    , C2
    , C3
    , C4
    , C5
    , C6
    )
    VALUES
    (
      1, 0, 1, 0, 1, 0, 0
    )
    ,(
      2, 0, 0, 0, 0, 0, 0
    )
    ,(
      3, 0, 1, 0, 1, 1, 1
    );

    SELECT *
    FROM #tbl t;

    I want to run an UPDATE query on this table, such that it ends up looking like this:

    SELECT *
    FROM
       (
        VALUES
         (1, 2, 4, NULL, NULL, NULL, NULL)
        ,    (2, NULL, NULL, NULL, NULL, NULL, NULL)
        ,    (3, 2, 4, 5, 6, NULL, NULL)
       ) Results (Id, C1, C2, C3, C4, C5, C6);[/code]

    Hope that's clear enough. The initial table contains 0s and 1s in columns C1 to C6. The desired table contains the column numbers containing the 1s, starting from C1 and working upwards.


    UPDATE t
    SET Ci=a.C1
       , C2= a.C2
       , C3 = a.C3
       , C4 = a.C4
       , C5 = a.C5
       , C6 = a.C6
    FROM (SELECT *
    FROM
       (
        VALUES
         (1, 2, 4, NULL, NULL, NULL, NULL)
        ,    (2, NULL, NULL, NULL, NULL, NULL, NULL)
        ,    (3, 2, 4, 5, 6, NULL, NULL)
       ) Results (Id, C1, C2, C3, C4, C5, C6)) a
    JOIN #tbl t ON t.ID=a.id; 

    You can't hard-code values in your UPDATE! I want an UPDATE statement which runs on the first version of the table and produces version 2.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • How many columns are in the actual data set?
    😎

  • there may be an easier way to do this, but I got stuck trying to figure out how to shift the values leftwise:


    WITH sortvals AS
    (SELECT t.Id, x.val, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS colnum
      FROM #tbl t
      CROSS APPLY (SELECT t.C1 AS val UNION ALL SELECT t.C2*2 AS val UNION ALL SELECT t.C3*3 AS val UNION ALL SELECT t.C4*4 AS val UNION ALL SELECT t.C5*5 AS val UNION ALL SELECT t.C6*6 AS val) x
      WHERE x.val > 0),
    updatevals AS
    (SELECT t.Id, MAX(CASE WHEN v.colnum = 1 THEN val END) AS C1,
        MAX(CASE WHEN v.colnum = 2 THEN val END) AS C2,
        MAX(CASE WHEN v.colnum = 3 THEN val END) AS C3,
        MAX(CASE WHEN v.colnum = 4 THEN val END) AS C4,
        MAX(CASE WHEN v.colnum = 5 THEN val END) AS C5,
        MAX(CASE WHEN v.colnum = 6 THEN val END) AS C6
      FROM #tbl t
        LEFT OUTER JOIN sortvals v ON t.Id = v.Id
      GROUP BY t.Id)
    UPDATE t SET
        C1 = u.C1
        C2 = u.C2
        C3 = u.C3
        C4 = u.C4
        C5 = u.C5
        C6 = u.C6
      FROM #tbl t
        INNER JOIN updatevals u ON t.Id = u.Id

  • This is elementary my dear Phil, your illness must have been quite serious 😉
    😎

    ;WITH BASE_DATA AS
    (
      SELECT
       T.Id
       ,X.CV
       ,ROW_NUMBER() OVER
        (
          PARTITION BY T.Id
          ORDER BY  ISNULL(X.CV,999999999)
        ) AS OUTPOS
      FROM #tbl T
      CROSS APPLY
      (
       SELECT CASE WHEN T.C1 = 1 THEN 1 END UNION ALL
       SELECT CASE WHEN T.C2 = 1 THEN 2 END UNION ALL
       SELECT CASE WHEN T.C3 = 1 THEN 3 END UNION ALL
       SELECT CASE WHEN T.C4 = 1 THEN 4 END UNION ALL
       SELECT CASE WHEN T.C5 = 1 THEN 5 END UNION ALL
       SELECT CASE WHEN T.C6 = 1 THEN 6 END
      )X(CV)
    )
    SELECT
     BD.Id
    ,MAX(CASE WHEN BD.OUTPOS = 1 THEN BD.CV END) AS CV1
    ,MAX(CASE WHEN BD.OUTPOS = 2 THEN BD.CV END) AS CV2
    ,MAX(CASE WHEN BD.OUTPOS = 3 THEN BD.CV END) AS CV3
    ,MAX(CASE WHEN BD.OUTPOS = 4 THEN BD.CV END) AS CV4
    ,MAX(CASE WHEN BD.OUTPOS = 5 THEN BD.CV END) AS CV5
    ,MAX(CASE WHEN BD.OUTPOS = 6 THEN BD.CV END) AS CV6
    FROM   BASE_DATA BD
    GROUP BY  BD.Id;

    Output

  • Id          CV1         CV2         CV3         CV4         CV5         CV6
    ----------- ----------- ----------- ----------- ----------- ----------- -----------
    1           2           4           NULL        NULL        NULL        NULL
    2           2           NULL        NULL        NULL        NULL        NULL
    3           2           4           5           6           NULL        NULL
  • Eirikur Eiriksson - Thursday, January 26, 2017 12:25 PM

    How many columns are in the actual data set?
    😎

    Six is the actual number.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin - Thursday, January 26, 2017 12:47 PM

    Eirikur Eiriksson - Thursday, January 26, 2017 12:25 PM

    How many columns are in the actual data set?
    😎

    Six is the actual number.

    Then you are good to go
    😎

  • Eirikur Eiriksson - Thursday, January 26, 2017 12:51 PM

    Phil Parkin - Thursday, January 26, 2017 12:47 PM

    Eirikur Eiriksson - Thursday, January 26, 2017 12:25 PM

    How many columns are in the actual data set?
    😎

    Six is the actual number.

    Then you are good to go
    😎

    Thanks, Eirikur

    Leaving aside the slightly noxious beginnator, that's nice code. My brain's just not firing properly today. Thanks again.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I started looking at this before all the other solutions got posted, and I'm pretty sure the code I came up with is ugly, but I'd actually like to know just how inefficient it is, and where in the code those inefficiencies are.   Explanations of why would also be appreciated.   Trying to learn how to avoid problems....


    CREATE TABLE #tbl (
        Id int PRIMARY KEY
        , C1 int
        , C2 int
        , C3 int
        , C4 int
        , C5 int
        , C6 int
    );
    INSERT #tbl (Id, C1, C2, C3, C4, C5, C6)
    VALUES    (1, 0, 1, 0, 1, 0, 0),
            (2, 0, 0, 0, 0, 0, 0),
            (3, 0, 1, 0, 1, 1, 1);

    DECLARE @LARGE_NUMBER AS int = 9999;

    WITH CTE AS (

        SELECT T.Id,
            CAST(T.C1 AS char(1)) + ':' + CAST(T.C2 AS char(1)) + ':' + CAST(T.C3 AS char(1)) + ':' + CAST(T.C4 AS char(1)) + ':' +
                CAST(T.C5 AS char(1)) + ':' + CAST(T.C6 AS char(1)) AS STRING,
            T.C1, T.C2, T.C3, T.C4, T.C5, T.C6
        FROM #tbl AS T
    ),
        NUMBERS AS (

            SELECT Id,
                NULLIF(ItemNumber, @LARGE_NUMBER) AS ItemNumber,
                ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ItemNumber) AS RN
            FROM (
                SELECT C.Id, S.ItemNumber
                FROM CTE AS C
                    CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
                WHERE S.Item = 1
                UNION ALL
                SELECT C.Id, @LARGE_NUMBER AS ItemNumber
                FROM CTE AS C
                    CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
                WHERE S.Item = 0
                ) AS X
    ),
        PIVOTED AS (
        
            SELECT Id, [1] AS NEW_C1, [2] AS NEW_C2, [3] AS NEW_C3, [4] AS NEW_C4, [5] AS NEW_C5, [6] AS NEW_C6
            FROM NUMBERS
                PIVOT (MAX(ItemNumber) FOR RN IN ([1],[2],[3],[4],[5],[6])) AS PVT
    )
    UPDATE T
    SET T.C1 = P.NEW_C1,
        T.C2 = P.NEW_C2,
        T.C3 = P.NEW_C3,
        T.C4 = P.NEW_C4,
        T.C5 = P.NEW_C5,
        T.C6 = P.NEW_C6
    FROM #tbl AS T
        INNER JOIN PIVOTED AS P
            ON T.Id = P.Id;

    SELECT *
    FROM #tbl
    ORDER BY Id;

    DROP TABLE #tbl;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Thursday, January 26, 2017 1:52 PM

    I started looking at this before all the other solutions got posted, and I'm pretty sure the code I came up with is ugly, but I'd actually like to know just how inefficient it is, and where in the code those inefficiencies are.   Explanations of why would also be appreciated.   Trying to learn how to avoid problems....


    CREATE TABLE #tbl (
        Id int PRIMARY KEY
        , C1 int
        , C2 int
        , C3 int
        , C4 int
        , C5 int
        , C6 int
    );
    INSERT #tbl (Id, C1, C2, C3, C4, C5, C6)
    VALUES    (1, 0, 1, 0, 1, 0, 0),
            (2, 0, 0, 0, 0, 0, 0),
            (3, 0, 1, 0, 1, 1, 1);

    DECLARE @LARGE_NUMBER AS int = 9999;

    WITH CTE AS (

        SELECT T.Id,
            CAST(T.C1 AS char(1)) + ':' + CAST(T.C2 AS char(1)) + ':' + CAST(T.C3 AS char(1)) + ':' + CAST(T.C4 AS char(1)) + ':' +
                CAST(T.C5 AS char(1)) + ':' + CAST(T.C6 AS char(1)) AS STRING,
            T.C1, T.C2, T.C3, T.C4, T.C5, T.C6
        FROM #tbl AS T
    ),
        NUMBERS AS (

            SELECT Id,
                NULLIF(ItemNumber, @LARGE_NUMBER) AS ItemNumber,
                ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ItemNumber) AS RN
            FROM (
                SELECT C.Id, S.ItemNumber
                FROM CTE AS C
                    CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
                WHERE S.Item = 1
                UNION ALL
                SELECT C.Id, @LARGE_NUMBER AS ItemNumber
                FROM CTE AS C
                    CROSS APPLY dbo.DelimitedSplit8K(C.STRING, ':') AS S
                WHERE S.Item = 0
                ) AS X
    ),
        PIVOTED AS (
        
            SELECT Id, [1] AS NEW_C1, [2] AS NEW_C2, [3] AS NEW_C3, [4] AS NEW_C4, [5] AS NEW_C5, [6] AS NEW_C6
            FROM NUMBERS
                PIVOT (MAX(ItemNumber) FOR RN IN ([1],[2],[3],[4],[5],[6])) AS PVT
    )
    UPDATE T
    SET T.C1 = P.NEW_C1,
        T.C2 = P.NEW_C2,
        T.C3 = P.NEW_C3,
        T.C4 = P.NEW_C4,
        T.C5 = P.NEW_C5,
        T.C6 = P.NEW_C6
    FROM #tbl AS T
        INNER JOIN PIVOTED AS P
            ON T.Id = P.Id;

    SELECT *
    FROM #tbl
    ORDER BY Id;

    DROP TABLE #tbl;

    On this small data set the difference between the row_number method I used and yours is substantial and likely to increase if the size of the set grows.
    😎

    The IO stats give a hint
    Eirikur
    Table 'Worktable'. Scan count 0, logical reads 0
    Table '#tbl'. Scan count 1, logical reads 2

    sgmunson
    Table '#tbl'. Scan count 2, logical reads 10

    I'll have a look at the execution plan when I have the time.

  • Phil Parkin - Thursday, January 26, 2017 12:58 PM

    Eirikur Eiriksson - Thursday, January 26, 2017 12:51 PM

    Phil Parkin - Thursday, January 26, 2017 12:47 PM

    Eirikur Eiriksson - Thursday, January 26, 2017 12:25 PM

    How many columns are in the actual data set?
    😎

    Six is the actual number.

    Then you are good to go
    😎

    Thanks, Eirikur

    Leaving aside the slightly noxious beginnator, that's nice code. My brain's just not firing properly today. Thanks again.

    You are very welcome Phil.

    My code is normally torn between the beginnator and the terminator 😀
    😎

  • Viewing 13 posts - 1 through 12 (of 12 total)

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