Dynamic SQL Column Value Duplicate and Difference Detection Merge Query

Dynamic SQL column value duplicate and difference detection merge query

1. Solve all three situations, the code below takes care of precedence, empty and possible null values in the content. INTO temp will save the query result into a table named temp.

   SELECT id, var1, var2, 
       IIf(Len(var1 & "") = 0, var2, var1) AS merged
   INTO temp
   FROM myTable;
   

2. Transfer the records into the source table.

   UPDATE myTable 
       INNER JOIN temp 
         ON [myTable].ID=temp.ID 
   SET myTable.merged= [temp].[merged];
   

The question "Combine values from n-fields with precedence" helped!

Covert a duplicate Row value into column using pivot table

This Worked well for me. :)

IF OBJECT_ID('TEMPDB.dbo.##FinalResult ') IS NOT NULL DROP TABLE ##FinalResult 

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Name])     
FROM (SELECT DISTINCT [Name] FROM #Result) AS ##FinalResult 

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT [Item], ' + @ColumnName + '
    INTO ##FinalResult 
    FROM #Result 
    PIVOT(MAX([Value])
    FOR [Name] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

SELECT * FROM ##FinalResult

How To Create Duplicate Records depending on Column which indicates on Repetition

You would need to first generate_rows with increasing row_number and then perform a cross join with your table.

Eg:

create table t(rowid int, name varchar(100),shares_bought int, date_val date)

insert into t
select *
from (values (1,'Dan',2,'2018-08-23')
            ,(2,'Mirko',1,'2018-08-25')
            ,(3,'Shuli',3,'2018-05-14')
            ,(4,'Regina',1,'2018-01-19')
            )t(x,y,z,a)

with generate_data
  as (select top (select max(shares_bought) from t)
             row_number() over(order by (select null)) as rnk /* This would generate rows starting from 1,2,3 etc*/
       from sys.objects a
       cross join sys.objects b
   )
select row_number() over(order by t.rowid) as rowid,t.name,1 as shares_bought,t.date_val
  from t 
  join generate_data gd
    on gd.rnk <=t.shares_bought /* generate rows up and until the number of shares bought*/
 order by 1

Here is a db fiddle link

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5736255585c3ab2c2964c655bec9e08b

look up dynamic value from range in another table

You may use Row_Number() function as the following to remove duplicates:

with cte as
(
  Select D.id,D.yeard,D.val, C.limitVal, 
  row_number() over (partition by D.id order by C.yeard desc) as rn from
  detailTbl D left join controlTbl C
  on D.yeard>=C.yeard
)

Select B.id,B.yeard,B.val,B.limitVal from
cte B where B.rn=1 order by B.id

See a demo on MySQL 8.0 from here.

Combine values from n-fields with precedence

You want var1 when it is not Null, and otherwise you want var2. You can use Nz to get that.

SELECT var1, var2, Nz(var1, var2) AS  merged
FROM myTable;

If you will be running the query from outside an Access session, the Nz function will not be available. In that case, you can use an IIf expression.

SELECT var1, var2, IIf(var1 Is Null, var2, var1) AS  merged
FROM myTable;

If var1 may contain zero-length strings (""), and you want those treated the same as Null --- meaning return var2 in that situation --- use this query ...

SELECT var1, var2, IIf(Len(var1 & "") = 0, var2, var1) AS  merged
FROM myTable;

Merge rows code returns empty content

Okay I finally solved it!

Here are the steps for this special case:

1. SELECT the id and the desired columns. Note that this expects the columns to both not contain values (I haven't tested it with both columns containing a value, whether the same or not).

   # query1
   SELECT id, col1 & col2 AS merged
   FROM table_name;
   

2. Insert the results of the query before into a temporary table for security (better save it that encounter errors).

   # query2
   SELECT * INTO temp
   FROM query1
   WHERE FirstOfVal;
   

3. Insert the contents of the temporary table into the main table.

   UPDATE table_name
       INNER JOIN temp ON table_name.id = temp.id
       SET table_name.merged = [temp].[merged];
   

You now have the merged columns into your source table.

---

Related Topics