none
Increment 2 columns side by side based on primarykey column in SQL RRS feed

  • Question

  • Hi,

    I have table like below.

    PrimaryKeyColumn Column1 Column2
    A String NULL
    B String String
    C NULL String
    D String String
    E String NULL

    I would like to get desired result like below.

    PrimaryKeyColumn Column1 Column2 Column1_New Column2_New
    A String NULL String1 NULL
    B String String String2 String3
    C NULL String NULL String4
    D String String String5 String6
    E String NULL String7 NULL

    ;WITH ItemDisplayOrder AS (
    	SELECT
    		*
    		,ROW_NUMBER() OVER(ORDER BY PrimaryKeyColumn) AS NewDisplayOrder
    	FROM table WHERE column1 LIKE '%String%')
    	SELECT CONCAT([column1],NewDisplayOrder),*  FROM ItemDisplayOrder

    But above is working for only first column. Please help me to get desired result.

    Thanks in advance.


    • Edited by Narsa Wednesday, September 18, 2019 2:19 AM
    Wednesday, September 18, 2019 1:50 AM

Answers

  • You could do it with a loop (cursor/while/etc) but here's a set based approach..

    DECLARE @table TABLE
        ([PrimaryKeyColumn] varchar(1), [Column1] varchar(6), [Column2] varchar(6))
    ;   
    INSERT INTO @table
        ([PrimaryKeyColumn], [Column1], [Column2])
    VALUES
        ('A', 'String', NULL),
        ('B', 'String', 'String'),
        ('C', NULL, 'String'),
        ('D', 'String', 'String'),
        ('E', 'String', NULL);
    
    ;with cteFlatten
    AS
    (
    	SELECT PrimaryKeyColumn, Column1 AS ColUnion, 1 as SetNumber FROM @table
    	UNION 
    	SELECT PrimaryKeyColumn, Column2 AS ColUnion, 2 as SetNumber FROM @table
    ),
     cteRank
    AS
    (
    	SELECT *, IIF(ColUnion IS NOT NULL, RANK() OVER(PARTITION BY colunion ORDER BY PrimaryKeyColumn,SetNumber ),null) as rn
    	FROM cteFlatten
    
    )
    select t.*, col1.ColUnion AS Column1_New, col2.ColUnion AS Column2_New
    from @table t
    outer apply (SELECT PrimaryKeyColumn, ColUnion + CAST(rn as nvarchar) AS ColUnion FROM cteRank where SetNumber = 1 AND t.PrimaryKeyColumn = cteRank.PrimaryKeyColumn ) col1
    outer apply (SELECT PrimaryKeyColumn, ColUnion + CAST(rn as nvarchar) AS ColUnion FROM cteRank where SetNumber = 2 AND t.PrimaryKeyColumn = cteRank.PrimaryKeyColumn ) col2
    order by t.PrimaryKeyColumn
    

    

    • Marked as answer by Narsa Thursday, September 19, 2019 1:30 AM
    Wednesday, September 18, 2019 12:37 PM

All replies

  • You could do it with a loop (cursor/while/etc) but here's a set based approach..

    DECLARE @table TABLE
        ([PrimaryKeyColumn] varchar(1), [Column1] varchar(6), [Column2] varchar(6))
    ;   
    INSERT INTO @table
        ([PrimaryKeyColumn], [Column1], [Column2])
    VALUES
        ('A', 'String', NULL),
        ('B', 'String', 'String'),
        ('C', NULL, 'String'),
        ('D', 'String', 'String'),
        ('E', 'String', NULL);
    
    ;with cteFlatten
    AS
    (
    	SELECT PrimaryKeyColumn, Column1 AS ColUnion, 1 as SetNumber FROM @table
    	UNION 
    	SELECT PrimaryKeyColumn, Column2 AS ColUnion, 2 as SetNumber FROM @table
    ),
     cteRank
    AS
    (
    	SELECT *, IIF(ColUnion IS NOT NULL, RANK() OVER(PARTITION BY colunion ORDER BY PrimaryKeyColumn,SetNumber ),null) as rn
    	FROM cteFlatten
    
    )
    select t.*, col1.ColUnion AS Column1_New, col2.ColUnion AS Column2_New
    from @table t
    outer apply (SELECT PrimaryKeyColumn, ColUnion + CAST(rn as nvarchar) AS ColUnion FROM cteRank where SetNumber = 1 AND t.PrimaryKeyColumn = cteRank.PrimaryKeyColumn ) col1
    outer apply (SELECT PrimaryKeyColumn, ColUnion + CAST(rn as nvarchar) AS ColUnion FROM cteRank where SetNumber = 2 AND t.PrimaryKeyColumn = cteRank.PrimaryKeyColumn ) col2
    order by t.PrimaryKeyColumn
    

    

    • Marked as answer by Narsa Thursday, September 19, 2019 1:30 AM
    Wednesday, September 18, 2019 12:37 PM
  • Thank you jmcmullen...
    Thursday, September 19, 2019 1:31 AM