SQL - Prepend a value if missing
how to add a value to existing value using sql query
sql stuff for xml path
sql update add to existing value string
sql: selecting rows where column value changed from previous row
how to use values from previous or next rows in a sql server query
DECLARE @T TABLE ( [Col1] VARCHAR(20) , [RowNum] INT ) ; INSERT INTO @T VALUES ( N'second', 1 ) , ( N'fifth', 4 ) , ( N'fourth', 3 ) --, ( N'zzz', 1 ) , ( N'third', 2 ) ---- OR when "zzz" is part of this list --VALUES -- ( N'second', 2 ) -- , ( N'fifth', 5 ) -- , ( N'fourth', 4 ) -- , ( N'zzz', 1 ) -- , ( N'third', 3 ) SELECT STUFF (( SELECT ',' + [SQ].[Col1] FROM ( SELECT N'zzz' AS [Col1] , 1 AS [RowNum] UNION SELECT [Col1] , [RowNum] FROM @T ) AS [SQ] FOR XML PATH ( '' ), TYPE ).[value] ( '.', 'varchar(MAX)' ), 1, 1, '' ) ;
Goal: Prepend "zzz," in the output string if missing in the 2nd part of the union AND the values should be in ASC ordered based on the values specified in [rownum] field defined in the 2nd part of the union. If "zzz" exists in the 2nd part of the input already (it will always be RowNum 1 in that case), it should return it only once as the first value.
UPDATED the requirement due to an error on my part when creating this post. Updated code/data represents more accurate scenario. Please note the RowNum seq in the 2nd part of the UNION, it also starts with 1, but this time, it might or might not be associated to "zzz" Basically, I want to prepend "zzz" in the comma-delimited & ordered output if it doesn't exist.
Attached the Answer for the updated Scenario-
DECLARE @T TABLE ( [Col1] VARCHAR(20) , [RowNum] INT ) ; INSERT INTO @T VALUES ( N'second', 1 ) , ( N'fifth', 4 ) , ( N'fourth', 3 ) --, ( N'zzz', 1 ) , ( N'third', 2 ) ---- OR when "zzz" is part of this list --VALUES -- ( N'second', 2 ) -- , ( N'fifth', 5 ) -- , ( N'fourth', 4 ) -- , ( N'zzz', 1 ) -- , ( N'third', 3 ) SELECT STUFF (( SELECT ',' + [SQ].[Col1] FROM ( SELECT N'zzz' AS [Col1] , 0 AS [RowNum] UNION SELECT [Col1] , [RowNum] FROM @T ) AS [SQ] ORDER BY [RowNum] FOR XML PATH ( '' ), TYPE ).[value] ( '.', 'varchar(MAX)' ), 1, 1, '' ) ;
10 SQL tricks that you didn't think were possible, This article will bring you 10 SQL tricks that many of you might not have thought were possible. In order to understand the value of these 10 SQL tricks, it is first We can append a simple FILTER clause to an aggregate function in However you are missing FROM DUAL in your first select statement as� Introduction This blog will help users update a record while keeping the existing record value if it exists. My initial requirement was to just update the record but after some time, there was a case in my project to update the same record from two different pages.
Hope the below one will help you.
SELECT ',' + [SQ].[Col1] FROM ( SELECT N'first' AS [Col1],1 AS [RowNum] UNION SELECT [ABC].[Col1],[ABC].[RowNum] FROM ( VALUES ( N'second', 2 ) , ( N'fifth', 5 ) , ( N'fourth', 4 ) --, ( N'first', 1 ) , ( N'third', 3 ) ) AS [ABC] ( [Col1], [RowNum] ) ) AS [SQ] ORDER BY [RowNum] FOR XML PATH ( '' ), TYPE ).[value] ( '.', 'varchar(MAX)' ), 1, 1, '' ) ;
Returns an output
STUFF (Transact-SQL), Is an integer value that specifies the location to start deletion and insertion. If start is negative or zero, a null string is returned. If start is longer� missing numeric value is usually expressed as a period ( .), but it also can be stated as one of other 27 special missing value expressions based on the underscore (_) and letters A, B,…,Z, that is, ._, .A, .B,…,.Z. In SAS SQL procedure, a particular missing value is equal to itself, but those 28 missing values are not equal to each other.
Common Table Expressions (CTEs) provide a handy way of breaking queries down into simpler steps. Note that you can view the results of each step by switching out the last
with Assortment as ( -- Start with the "input" rows. select Col1, RowNum from ( values ( N'second', 2 ), ( N'fifth', 5 ), ( N'fourth', 4 ), -- ( N'first', 1 ), ( N'third', 3 ) ) ABC ( Col1, RowNum ) ), ExtendedAssortment as ( -- Conditionally add "first". select Col1, RowNum from Assortment union all -- Do not remove duplicate rows. select N'first', 1 where not exists ( select 42 from Assortment where Col1 = N'first' ) ) -- Output the result. -- Intermediate results may be seen by uncommenting one of the alternate select statements. -- select * from Assortment; -- select * from ExtendedAssortment; select Stuff( ( select N',' + Col1 from ExtendedAssortment order by RowNum for XML path(N''), type).value( N'.', 'NVarChar(max)' ), 1, 1, N'' ) as List;
The same logic can be performed using tables for input:
-- Rows to be included in the comma delimited string. declare @Input as Table ( Col1 NVarChar(20), RowNum Int ); insert into @Input ( Col1, RowNum ) values ( N'second', 2 ), ( N'fifth', 5 ), --( N'ZZZ', 17 ), -- Test row. ( N'fourth', 4 ), ( N'third', 3 ); select * from @Input; -- Mandatory value that must appear in the result. One row only. declare @Mandatory as Table ( Col1 NVarChar(20), RowNum Int ); -- By using the maximum negative value for an Int this value will be prepended -- (unless other rows happen to have the same RowNum value). insert into @Mandatory ( Col1, RowNum ) values ( N'ZZZ', -2147483648 ); select * from @Mandatory; -- Process the data. with AllRows as ( select Col1, RowNum from @Input union all select Col1, RowNum from @Mandatory where not exists ( select 42 from @Mandatory as M inner join @Input as I on M.Col1 = I.Col1 ) ) -- Output the result. -- Intermediate results may be seen by uncommenting the alternate select statement. --select * from AllRows; select Stuff( ( select N',' + Col1 from AllRows order by RowNum for XML path(N''), type).value( N'.', 'NVarChar(max)' ), 1, 1, N'' ) as List;
INSERT INTO statement (Microsoft Access SQL), The name of the table or query to append records to. Each value is inserted into the field that corresponds to the value's position When you do not specify each field, the default value or Null is inserted for missing columns. SQL JOINs make it easy to find and fix missing data by Jeff Davis in Data Management on April 30, 2002, 12:00 AM PST Many people are confused by various JOIN operations supported by SQL.
Append A Value To The Existing Value In SQL Server, If the value exists, then append the new value with comma separation. Update salary will do the sum of another salary with existing salary. Create� The replicate T-SQL function in Microsoft’s SQL Server makes it simple to add leading zeros to a number value. Let’s create a table ‘emp’ with empid and salary columns. create table emp ( empid int, salary int ); Insert some sample data into table ‘emp’. insert into emp values (1,300); insert into emp values […]
Reading, Combining, and Modifying SAS Data Sets : Combining , The values of the variables in the program data vector are then set to missing, You can also use the SQL language to concatenate tables. Note: You cannot use PROC APPEND to add observations to a SAS data set in a sequential library. Is it possible to write a query to fill in the missing value of a column with the last known value. For example, the following dataset has values for the years 2010,11,12,15 & 16; but does have values for the year 2013,14 & 18. Year Value---- -----2010 A 2011 B 2012 C 2013 2014 2015 P 2016 Q 2018
Merge, join, and concatenate — pandas 0.20.3 documentation, It is worth noting however, that concat (and therefore append ) makes a full copy of the Users who are familiar with SQL but new to pandas might be interested in a Of course if you have missing values that are introduced, then the resulting � Good day, I have a column I query on ENTPRNUM and I want to add either one or two zeros before the numbers in my SQL query result depending on length of the value. Column results have a max of 7 digit numbers in it but at times it will only have 5, 6 or 7. So when a number result is 12345 I want it to show up as 0012345, or when it's 123456 I want
ORDER BY RowNumto your
FOR XML PATHquery. You don't supply an order, so SQL Server is free to put them in what ever order it "feels like".
- Can you also add
1 as RowNumto
SELECT N'first' AS [Col1]part?
- You should explicitly state the issue(s) to be addressed, i.e. that the output order is not to your liking. Curiously, you solved the conditional prepending issue by using
union allsince it eliminates duplicate rows.
- Thank you all for your reply. Based on the original code, your inputs were completely valid, but due to a mistake on my part when creating this post, I didn't post accurate scenario. Updated OP.
- Thank you for your feedback. It solves the problem I originally posted. However, it was my mistake that I posted somewhat inaccurate scenario. With the updated scenario, is it possible to modify your code to fix it? I tried to play around with it and was unsuccessful. Thanks again! Cheers
- Thank you for your feedback. It solves the problem I originally posted. However, it was my mistake that I posted somewhat inaccurate scenario. I'm going to play around with the updated scenario to see if this CTE approach works. Thanks again! Cheers
- @007 You can replace
select N'first', 1with the value you want to prepend and a number that will force it to the beginning of the list, be it
-2147483648(-2^31). Change the
where not existsvalue from
N'first'to the value to be checked for in the input, e.g.
N'ZZZ', and Bob's your uncle. I've added a variation of the code to handle input from tables.