none
how can I fill the blank space in the example below?

    Question

  • I have to fill using sql statement the blank space with the same description that appears below that belong to the same STTCKT. I am using a temporary table.


    Friday, April 11, 2014 9:15 PM

Answers

  • Do you want to update your temp table?

    CREATE TABLE #test (
    	[STBK#] VARCHAR(50)
    	,STTCKT INT
    	)
    
    INSERT INTO #test
    VALUES (
    	NULL
    	,48035
    	)
    	,(
    	'First Two Truck'
    	,48035
    	)
    	,(
    	NULL
    	,48039
    	)
    	,(
    	'First Two Truck'
    	,48039
    	)
    	,(
    	NULL
    	,48046
    	)
    	,(
    	'First Two Truck'
    	,48046
    	)
    	,(
    	NULL
    	,48054
    	)
    	,(
    	'KLS LOGISTIC, INC.'
    	,48054
    	);
    
    MERGE #Test AS Target
    USING (
    	SELECT *
    	FROM #Test
    	WHERE [STBK#] <> ''
    		AND [stbk#] IS NOT NULL
    	) AS Source
    	ON Target.Sttckt = Source.Sttckt
    WHEN MATCHED
    	AND (
    		Target.[STBK#] = ''
    		OR Target.[STBK#] IS NULL
    		)
    	THEN
    		UPDATE
    		SET [Stbk#] = Source.[Stbk#];
    SELECT *
    FROM #test;
    
    DROP TABLE #test;
    

    The above assumes you always have just 1 extra row with non-empty description.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, April 11, 2014 9:44 PM
    Moderator
  • CREATE TABLE test( [STBK#] varchar(50), STTCKT int)
    
    Insert into test values (null, 48035),('First Two Truck', 48035)
    ,(null, 48039),('First Two Truck', 48035)
    ,(null, 48046),('First Two Truck', 48046)
    ,(null, 48054),('KLS LOGISTIC, INC.', 48054)
     
    ;with mycte as
    (
    select [STBK#] , STTCKT,   row_number()over(order by STTCKT,[STBK#]  ) rn
    from test t 
     
    )
     
      
    Select sn as [STBK#],  m.STTCKT from mycte m
     
    Cross APPLY (SELECT TOP 1 [STBK#] FROM   mycte m3 WHERE  m3.rn>= m.rn AND [STBK#] IS NOT NULL  ORDER  BY rn  ) d3(sn)
     
     
     
    drop table test

    Friday, April 11, 2014 9:47 PM
    Moderator
  • There is no need in ROW_NUMBER. You can use simple query like below sample which cost 6% relative to the use of ROW_NUMBER which cost 94% (sort is havy operation with no index).

    DDL+DML:

    CREATE TABLE test( [STBK#] varchar(50), STTCKT int)
    
    truncate table test
    Insert into test values 
    	(null, 48035)
    	,('First Two Truck', 48035)
    	,(null, 48039)
    	,('First Two Truck', 48039)
    	,(null, 48046)
    	,('First Two Truck', 48046)
    	,(null, 48054)
    	,('KLS LOGISTIC, INC.', 48054)
    GO
    
    select * from test
    GO

    If all you need is SELECT with FILL then check this:

    select 
    	--[STBK#] , 
    	STTCKT, [Fixed_STBK#]
    from test T1
    Cross APPLY (SELECT TOP 1 T.[STBK#] FROM test T WHERE T.STTCKT = T1.STTCKT and T.STBK# IS NOT NULL) T2 ([Fixed_STBK#])

    And you can use this query as well without CROSS APPLY

    select 
    	--T1.[STBK#] , 
    	T1.STTCKT, T2.[Fixed_STBK#]
    from test T1
    LEFT JOIN (SELECT T.STTCKT,MAX(T.[STBK#]) [Fixed_STBK#] FROM test T GROUP BY T.STTCKT) T2 ON T1.STTCKT = T2.STTCKT

    If you are looking for update the original table please check this:

    UPDATE test
    SET [STBK#] = T2.[Fixed_STBK#]
    FROM test T1
    Cross APPLY (SELECT TOP 1 T.[STBK#] FROM test T WHERE T.STTCKT = T1.STTCKT and T.[STBK#] IS NOT NULL) T2 ([Fixed_STBK#])
    WHERE T1.[STBK#] IS NULL
    GO

    I hope this is helpful and answer your need, If not please try to clrify your needs and post your DDL+DML


    [Personal Site] [Blog] [Facebook]signature

    Saturday, April 12, 2014 12:37 AM
    Moderator
  • UPDATE T1
    SET T1.STBK#=T2.STBK#
    FROM #test T1 JOIN #test T2 ON T1.STTCKT=T2.STTCKT
    WHERE T2.STBK# IS NOT NULL

    Use Naomi's DDL for testing. Thanks Naomi.

    Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

    Monday, April 14, 2014 7:44 AM

All replies

  • Do you want to update your temp table?

    CREATE TABLE #test (
    	[STBK#] VARCHAR(50)
    	,STTCKT INT
    	)
    
    INSERT INTO #test
    VALUES (
    	NULL
    	,48035
    	)
    	,(
    	'First Two Truck'
    	,48035
    	)
    	,(
    	NULL
    	,48039
    	)
    	,(
    	'First Two Truck'
    	,48039
    	)
    	,(
    	NULL
    	,48046
    	)
    	,(
    	'First Two Truck'
    	,48046
    	)
    	,(
    	NULL
    	,48054
    	)
    	,(
    	'KLS LOGISTIC, INC.'
    	,48054
    	);
    
    MERGE #Test AS Target
    USING (
    	SELECT *
    	FROM #Test
    	WHERE [STBK#] <> ''
    		AND [stbk#] IS NOT NULL
    	) AS Source
    	ON Target.Sttckt = Source.Sttckt
    WHEN MATCHED
    	AND (
    		Target.[STBK#] = ''
    		OR Target.[STBK#] IS NULL
    		)
    	THEN
    		UPDATE
    		SET [Stbk#] = Source.[Stbk#];
    SELECT *
    FROM #test;
    
    DROP TABLE #test;
    

    The above assumes you always have just 1 extra row with non-empty description.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, April 11, 2014 9:44 PM
    Moderator
  • CREATE TABLE test( [STBK#] varchar(50), STTCKT int)
    
    Insert into test values (null, 48035),('First Two Truck', 48035)
    ,(null, 48039),('First Two Truck', 48035)
    ,(null, 48046),('First Two Truck', 48046)
    ,(null, 48054),('KLS LOGISTIC, INC.', 48054)
     
    ;with mycte as
    (
    select [STBK#] , STTCKT,   row_number()over(order by STTCKT,[STBK#]  ) rn
    from test t 
     
    )
     
      
    Select sn as [STBK#],  m.STTCKT from mycte m
     
    Cross APPLY (SELECT TOP 1 [STBK#] FROM   mycte m3 WHERE  m3.rn>= m.rn AND [STBK#] IS NOT NULL  ORDER  BY rn  ) d3(sn)
     
     
     
    drop table test

    Friday, April 11, 2014 9:47 PM
    Moderator
  • There is no need in ROW_NUMBER. You can use simple query like below sample which cost 6% relative to the use of ROW_NUMBER which cost 94% (sort is havy operation with no index).

    DDL+DML:

    CREATE TABLE test( [STBK#] varchar(50), STTCKT int)
    
    truncate table test
    Insert into test values 
    	(null, 48035)
    	,('First Two Truck', 48035)
    	,(null, 48039)
    	,('First Two Truck', 48039)
    	,(null, 48046)
    	,('First Two Truck', 48046)
    	,(null, 48054)
    	,('KLS LOGISTIC, INC.', 48054)
    GO
    
    select * from test
    GO

    If all you need is SELECT with FILL then check this:

    select 
    	--[STBK#] , 
    	STTCKT, [Fixed_STBK#]
    from test T1
    Cross APPLY (SELECT TOP 1 T.[STBK#] FROM test T WHERE T.STTCKT = T1.STTCKT and T.STBK# IS NOT NULL) T2 ([Fixed_STBK#])

    And you can use this query as well without CROSS APPLY

    select 
    	--T1.[STBK#] , 
    	T1.STTCKT, T2.[Fixed_STBK#]
    from test T1
    LEFT JOIN (SELECT T.STTCKT,MAX(T.[STBK#]) [Fixed_STBK#] FROM test T GROUP BY T.STTCKT) T2 ON T1.STTCKT = T2.STTCKT

    If you are looking for update the original table please check this:

    UPDATE test
    SET [STBK#] = T2.[Fixed_STBK#]
    FROM test T1
    Cross APPLY (SELECT TOP 1 T.[STBK#] FROM test T WHERE T.STTCKT = T1.STTCKT and T.[STBK#] IS NOT NULL) T2 ([Fixed_STBK#])
    WHERE T1.[STBK#] IS NULL
    GO

    I hope this is helpful and answer your need, If not please try to clrify your needs and post your DDL+DML


    [Personal Site] [Blog] [Facebook]signature

    Saturday, April 12, 2014 12:37 AM
    Moderator
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Posting colored pictures makes you look like a child with wax crayons. A forum is not a good place for a child to learn RDBMS. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, April 12, 2014 2:34 AM
  • UPDATE T1
    SET T1.STBK#=T2.STBK#
    FROM #test T1 JOIN #test T2 ON T1.STTCKT=T2.STTCKT
    WHERE T2.STBK# IS NOT NULL

    Use Naomi's DDL for testing. Thanks Naomi.

    Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

    Monday, April 14, 2014 7:44 AM