none
MS SQL : Need to get row wise value from cell for similar rows RRS feed

  • Question

  • I have 100s of excel sheets(CSV files) I need to use for historical load. I am going to load all the CSVs to a stage table using ETL.

    I am trying to get the row wise value from cell for similar rows. Please see snapshot below for more clear description.

    I got the 1st three sections correct but others I got wrong. the red cross sections are wrong as I need to get the date in the row section. 

    My code:

    ALTER TABLE dbo.[sql GET VALUES] ADD rowId INT IDENTITY(1, 1)
    ALTER TABLE dbo.[sql GET VALUES] ADD RequiredColumn VARCHAR(255) NULL
    
    UPDATE a
    SET requiredColumn = column1
    FROM dbo.[sql GET VALUES] a
    WHERE column1 LIKE '%Sunday%'
          OR column1 LIKE '%Monday%'
          OR column1 LIKE '%tuesday%'
          OR column1 LIKE '%wednesday%'
          OR column1 LIKE '%thursday%'
          OR column1 LIKE '%friday%'
          OR column1 LIKE '%saturday%'
    
    IF OBJECT_ID('Tempdb.dbo.#temptable') IS NOT NULL
    BEGIN
        DROP TABLE tempdb.dbo.#temptable
    END
    
    SELECT rowID,
           CASE
               WHEN CONVERT(VARCHAR(255), requiredcolumn) IS NOT NULL THEN
                   CONVERT(VARCHAR(255), requiredcolumn)
               ELSE
           (
               SELECT MAX(requiredcolumn)
               FROM dbo.[sql GET VALUES]
               WHERE rowID <= t.rowID - 1
           )
           END AS requiredcolumn
    INTO #temptable
    FROM dbo.[sql GET VALUES] t
    
    UPDATE a
    SET a.requiredcolumn = i.requiredcolumn
    -- select a.requiredcolumn, i.requiredcolumn,* 
    FROM #temptable i
        INNER JOIN dbo.[sql GET VALUES] a
            ON i.rowID = a.rowID
    
    SELECT *
    FROM [sql GET VALUES]

    Please anyone help me what am i getting wrong with my SQL code?

    Sample data here.

    Wednesday, April 24, 2019 7:29 AM

Answers

All replies

  • It is unclear. Do you mean you have to get  7 March and 8 March where red cross section?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 24, 2019 7:57 AM
    Answerer
  • CREATE TABLE #C (dt date ,rowid int,col date)
    INSERT INTO #C values('20190304', 1,NULL)
    INSERT INTO #C values(NULL, 2,NULL)
    INSERT INTO #C values(NULL, 3,NULL)
    INSERT INTO #C values(NULL, 4,NULL)


    INSERT INTO #C values('20190305', 5,NULL)
    INSERT INTO #C values(NULL, 6,NULL)
    INSERT INTO #C values(NULL, 7,NULL)

    INSERT INTO #C values('20190306', 8,NULL)
    INSERT INTO #C values(NULL, 9,NULL)





    SELECT CASE WHEN dt is not null
                THEN dt
                ELSE (SELECT max(dt)
                      FROM #C
                      WHERE rowid <= t.rowid)
           END AS dt,
           rowid
    FROM #C t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 24, 2019 8:02 AM
    Answerer
  • More precise solution

    CREATE TABLE #C (col varchar(10) ,rowid int,col1 date)
    INSERT INTO #C values('20190304', 1,NULL)
    INSERT INTO #C values('a', 2,NULL)
    INSERT INTO #C values('b', 3,NULL)
    INSERT INTO #C values('c', 4,NULL)


    INSERT INTO #C values('20190305', 5,NULL)
    INSERT INTO #C values('a', 6,NULL)
    INSERT INTO #C values('b', 7,NULL)
    INSERT INTO #C values('c', 8,NULL)

    INSERT INTO #C values('20190306', 9,NULL)
    INSERT INTO #C values('a', 10,NULL)
    INSERT INTO #C values('b', 11,NULL)
    INSERT INTO #C values('c', 12,NULL)


    UPDATE #C SET col1=col where isdate(col)=1
    SELECT * FROM #C 


    SELECT col,CASE WHEN col1 is not null
                THEN col1
                ELSE (SELECT max(col1)
                      FROM #C
                      WHERE rowid <= t.rowid)
           END AS col,
           rowid
    FROM #C t


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 24, 2019 8:08 AM
    Answerer
  • Hey Uri,

    Yes, my code is correct for 4th, 5th and 6th April but somehow incorrect after that. I tried your code and it works for the value you kept but if I keep values from my table it won't work. Please see code below: 

    ```

    DROP TABLE #c
    CREATE TABLE #C (col varchar(255) ,rowid int,col1 varchar(255))
    INSERT INTO #C values('Wednesday, March 06, 2019 - Weather: TY: 15‹C', 1,NULL)
    INSERT INTO #C values('a', 2,NULL)
    INSERT INTO #C values('b', 3,NULL)
    INSERT INTO #C values('c', 4,NULL)


    INSERT INTO #C values('Thursday, March 07, 2019 - Weather: TY: 12‹C', 5,NULL)
    INSERT INTO #C values('a', 6,NULL)
    INSERT INTO #C values('b', 7,NULL)
    INSERT INTO #C values('c', 8,NULL)

    INSERT INTO #C values('Friday, March 08, 2019 - Weather: TY: 12‹C', 9,NULL)
    INSERT INTO #C values('a', 10,NULL)
    INSERT INTO #C values('b', 11,NULL)
    INSERT INTO #C values('c', 12,NULL)


    UPDATE #C SET col1=col where LEN(col) > '3' -- this is just to get the col1 value to get date info.
    SELECT * FROM #C 


    SELECT col,CASE WHEN col1 is not null
                THEN col1
                ELSE (SELECT max(col1)
                      FROM #C
                      WHERE rowid <= t.rowid)
           END AS col,
           rowid
    FROM #C t

    ```

    Wednesday, April 24, 2019 8:35 AM
  • Here you go

    UPDATE #C SET col1=col where LEN(col) > '3' -- this is just to get the col1 value to get date info.

    WITH cte
    AS
    (
    SELECT * ,
    grp=MAX(IIF(Col1 IS NOT NULL,rowid,NULL)) OVER (ORDER BY rowid) FROM #C 
    )    SELECT
             col
            ,grp
            ,col1 = MAX(col1) OVER (PARTITION BY grp ORDER BY rowid)
        FROM cte


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, April 24, 2019 8:49 AM
    Answerer
  • Hey Uri,

    Thank you so much for your answer. I am not familiar with CTE but your code is just magic for me :)

    One more thing, Could you please let me know how I can insert the data into a table from CTE? I tried but couldn't do so. Here is my code. 

    UPDATE #C
    SET col1 = col
    WHERE LEN(col) > '3' -- this is just to get the col1 value to get date info.

    CREATE TABLE #data ( col VARCHAR(255), grp INT, col1 VARCHAR(255)) 

    ;WITH cte
    AS (SELECT *,
               grp = MAX(IIF(col1 IS NOT NULL, rowid, NULL)) OVER (ORDER BY rowid ROWS UNBOUNDED PRECEDING)
        FROM #C)
    SELECT col,
           grp,
           col1 = MAX(col1) OVER (PARTITION BY grp ORDER BY rowid ROWS UNBOUNDED PRECEDING)
    FROM cte
    INSERT INTO #data ( col, grp, col1)
    SELECT * FROM cte 

    result : 

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (12 rows affected)
    Msg 208, Level 16, State 1, Line 62
    Invalid object name 'cte'.

    Wednesday, April 24, 2019 9:21 AM
  • See

    WITH cte
    AS (SELECT *,
               grp = MAX(IIF(col1 IS NOT NULL, rowid, NULL)) OVER (ORDER BY rowid ROWS UNBOUNDED PRECEDING)
        FROM #C)

    INSERT INTO #data ( col, grp, col1)
    SELECT col,
           grp,
           col1 = MAX(col1) OVER (PARTITION BY grp ORDER BY rowid ROWS UNBOUNDED PRECEDING)
    FROM cte


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 24, 2019 9:28 AM
    Answerer
  • Thank you so so much for all your answers. You made my day.

    Have a great day ahead.

    Cheers and Regards,

    Success

    Wednesday, April 24, 2019 10:30 AM