logo
down
shadow

How to Concatenate multiple columns if not empty


How to Concatenate multiple columns if not empty

By : Calis
Date : November 14 2020, 04:51 PM
it helps some times I want to concatenate the values of 9 columns into 1 column with | between the values. The problem is that some of the columns are empty for some of the rows, making it pretty ugly to use the =CONCATENATE() function as you would need to check =if(A2="";...) for every of the 9 columns. , You could use a simple UDF:
code :
Function MyConcat(ConcatArea As Range) As String
  For Each x In ConcatArea: xx = IIf(x = "", xx & "", xx & x & "|"): Next
  MyConcat = Left(xx, Len(xx) - 1)
End Function
Function MyConcat(ConcatArea As Range) As String
  For Each x In ConcatArea: xx = IIf(x = "" Or InStr(1, xx, x & "|") > 0, xx & "", xx & x & "|"): Next
  MyConcat = Left(xx, Len(xx) - 1)
End Function


Share : facebook icon twitter icon
How to fetch multiple columns from multiple table and show data on non empty columns and null or zero on empty columns u

How to fetch multiple columns from multiple table and show data on non empty columns and null or zero on empty columns u


By : Ernesto Mendoza Mich
Date : March 29 2020, 07:55 AM
will help you I would like to know how to fetch multiple columns from multiple table even if one column in one table does not exist and return the columns which ever exist in one or more table used for joins , I would like to suggest this query instead.
code :
SELECT
      upd.first_name
    , upd.last_name
    , u.username
    , pp.file_name
FROM urname u
LEFT OUTER JOIN user_pr_details a ON u.urname_id = upd.user_id
LEFT OUTER JOIN profile_pic pp ON u.urname_id = pp.user_id
WHERE u.urname_id  IN (7, 8, 9)
SQL Server: SELECT 4 non-empty columns and concatenate them

SQL Server: SELECT 4 non-empty columns and concatenate them


By : MaxKaye
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I don't really get why you want to pad these up to 163 characters, but here's an example that shows all possible combinations of empty and non-empty addresses:
code :
declare @MailsTable table
(
    RowNumber int,
    Mail1 varchar(40),
    Mail2 varchar(40),
    Mail3 varchar(40),
    Mail4 varchar(40)
);

insert @MailsTable values
    (0, null, null, null, null),
    (1, 'Addr1', null, null, null),
    (2, null, 'Addr2', null, null),
    (3, 'Addr1', 'Addr2', null, null),
    (4, null, null, 'Addr3', null),
    (5, 'Addr1', null, 'Addr3', null),
    (6, null, 'Addr2', 'Addr3', null),
    (7, 'Addr1', 'Addr2', 'Addr3', null),
    (8, null, null, null, 'Addr4'),
    (9, 'Addr1', null, null, 'Addr4'),
    (10, null, 'Addr2', null, 'Addr4'),
    (11, 'Addr1', 'Addr2', null, 'Addr4'),
    (12, null, null, 'Addr3', 'Addr4'),
    (13, 'Addr1', null, 'Addr3', 'Addr4'),
    (14, null, 'Addr2', 'Addr3', 'Addr4'),
    (15, 'Addr1', 'Addr2', 'Addr3', 'Addr4');

with ConcatenatedAddressesCTE as
(
    select
        RowNumber,
        Mails =
            case when Mail1 != '' then ';' + Mail1 else '' end +
            case when Mail2 != '' then ';' + Mail2 else '' end +
            case when Mail3 != '' then ';' + Mail3 else '' end +
            case when Mail4 != '' then ';' + Mail4 else '' end
    from
        @MailsTable
    where
        Mail1 != '' or Mail2 != '' or Mail3 != '' or Mail4 != ''
)
select
    RowNumber,
    FormattedMails = substring(Mails, 2, 200) + replicate(' ', 164 - len(Mails))
from
    ConcatenatedAddressesCTE;
RowNumber  FormattedMails
1          Addr1                                                                                                                                                              
2          Addr2                                                                                                                                                              
3          Addr1;Addr2                                                                                                                                                        
4          Addr3                                                                                                                                                              
5          Addr1;Addr3                                                                                                                                                        
6          Addr2;Addr3                                                                                                                                                        
7          Addr1;Addr2;Addr3                                                                                                                                                  
8          Addr4                                                                                                                                                              
9          Addr1;Addr4                                                                                                                                                        
10         Addr2;Addr4                                                                                                                                                        
11         Addr1;Addr2;Addr4                                                                                                                                                  
12         Addr3;Addr4                                                                                                                                                        
13         Addr1;Addr3;Addr4                                                                                                                                                  
14         Addr2;Addr3;Addr4                                                                                                                                                  
15         Addr1;Addr2;Addr3;Addr4                                                                                                                                            
Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic

Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic


By : william_soreham
Date : March 29 2020, 07:55 AM
To fix this issue Here is a dynamic way
Slightly different approach from your static query. To make it dynamic, Use while loop or CURSOR to generate the Task1 + Task2 + ..TaskN. Then use it in Select query.
code :
DECLARE @columns VARCHAR(50)='Task1,Task2,Task3', -- Pass the list of column names 
        @int     INT = 1,
        @sql     VARCHAR(8000)

SET @sql = '    ;WITH cte
         AS (SELECT *,
                    ''Cycle-'' + Cast(CycleNum AS VARCHAR(10)) + ''::'' '

WHILE @int <= Len(@columns) - Len(Replace(@columns, ',', '')) -- To find the number of Tasks in list
  BEGIN
      SET @sql += + '+''Task' + Cast(@int AS VARCHAR(10))
                  + '~''+' + 'Cast(Task'
                  + Cast(@int AS VARCHAR(10))
                  + ' AS VARCHAR(50)) + '','''
      SET @int += 1
  END

SET @sql += '   AS concat_dates
             FROM   #tempTable)
    SELECT DISTINCT FacilityName,
                    LEFT(CycleData, Len(CycleData) - 1)
    FROM   cte a
           CROSS apply(SELECT b.concat_dates + '',''
                       FROM   cte b
                       WHERE  a.FacilityName = b.FacilityName
                       FOR xml path('''')) cs (CycleData) 

                       '
--print @sql -- uncomment it to debug if you have any error when executing dynamic code
EXEC (@sql) 
;WITH cte
     AS (SELECT *,
                'Cycle-' + Cast(CycleNum AS VARCHAR(10))
                + '::' + 'Task1~' + Cast(Task1 AS VARCHAR(50))
                + ',' + 'Task2~' + Cast(Task2 AS VARCHAR(50))
                + ',' AS concat_dates
         FROM   #tempTable)
SELECT DISTINCT FacilityName,
                LEFT(CycleData, Len(CycleData) - 1)
FROM   cte a
       CROSS apply(SELECT b.concat_dates + ','
                   FROM   cte b
                   WHERE  a.FacilityName = b.FacilityName
                   FOR xml path('')) cs (CycleData) 
Pandas, concatenate certain columns if other columns are empty

Pandas, concatenate certain columns if other columns are empty


By : user7679920
Date : March 29 2020, 07:55 AM
To fix the issue you can do I've got a CSV file that is supposed to look like this: , Given your sample input:
code :
df = pd.DataFrame({
    'ID': ['Truck1', '', '', 'Car 5', ''],
    'years_active': [8, '', '', 3, ''],
    'issues': ['In dire need', 'of a', 'paintjob', 'To small for', 'large groups']
})
new_df = df.groupby(df.ID.replace('', method='ffill')).agg({'years_active': 'first', 'issues': ' '.join})
        years_active                      issues
ID                                              
Car 5              3   To small for large groups
Truck1             8  In dire need of a paintjob
How to Concatenate multiple columns with a comma if all are empty then showing #value

How to Concatenate multiple columns with a comma if all are empty then showing #value


By : Krishan
Date : March 29 2020, 07:55 AM
This might help you There are really two fixes you can apply in your case:
The first fix is to modify your UDF to detect the error condition before it returns, then just make sure your UDF returns the string "-":
Related Posts Related Posts :
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org