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, ''
              ) ;

Returns

zzz,second,third,fourth,fifth

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

first,second,third,fourth,fifth

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 select statement.

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'.[1]', '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'.[1]', '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

Comments

• Add ORDER BY RowNum to your FOR XML PATH query. 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 RowNum to 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 instead of union all since 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', 1 with the value you want to prepend and a number that will force it to the beginning of the list, be it 1, 0 or -2147483648 (-2^31). Change the where not exists value 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.