Concatenation Three column with condition

Debashish Mahonta

1y1.1kSQL

My datatable

Date	Column1	Column2	Column3
01/10/2024	Data1	Data2	NULL
01/10/2024	Data4	Data5	Data7
02/10/2024	Data8	Data9	NULL
02/10/2024	Data1	Data2	NULL

I want to concate Column1 and Column2 into one line if Column3 value is empty or NULL. If Column3 value is not empty or NULL then concate Column3 Only. When Column3 value available it concate only column3. How can i do it like below.

Date	Concate
01/10/2024	Data1, Data2
01/10/2024	Data7
02/10/2024	Data8, Data9, Data1, Data2

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty1y ago

Accepted answer

Hey Debashish, try this:

SELECT 
    [Date],
    STRING_AGG(
        CASE 
            WHEN Column3 IS NULL THEN Column1 + ', ' + Column2
            ELSE Column3
        END, ', '
    ) AS Concate
FROM your_table
GROUP BY [Date], Column3;

Jignesh Kumar1y ago

Hello,

You can use below query,

SELECT 
    Date,
    STRING_AGG(
        CASE 
            WHEN Column3 IS NULL THEN Column1 + ', ' + Column2
            ELSE Column3
        END, ', '
    ) AS Concate
FROM 
    your_table
GROUP BY 
    Date
ORDER BY 
    Date;

Vinoth Xavier1y ago

Hi Debashish Mahonta,

Please check this.



--CREATE TABLE DUMMY (DATE DATE,COLUMN1 VARCHAR(50),COLUMN2 VARCHAR(50),COLUMN3 VARCHAR(50))

SELECT *  FROM DUMMY
--INSERT INTO DUMMY VALUES ('01/10/2024','DATA 1','DATA 2',NULL)
--INSERT INTO DUMMY VALUES ('01/10/2024','DATA 4','DATA 5','DATA 7')
--INSERT INTO DUMMY VALUES ('02/10/2024','DATA 8','DATA 9',NULL)
--INSERT INTO DUMMY VALUES ('02/10/2024','DATA 1','DATA 2',NULL)
 --INSERT INTO DUMMY VALUES ('03/10/2024','DATA 4','DATA 5','DATA 37')
 --INSERT INTO DUMMY VALUES ('04/10/2024','DATA 44','DATA 45',NULL)
 --INSERT INTO DUMMY VALUES ('04/10/2024','DATA 54','DATA 56',NULL)

 --INSERT INTO DUMMY VALUES ('01/10/2024','RE DATA 1',' RE DATA 2',NULL)

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

CREATE TABLE TEMPDB..#TEMPTABLE1 ([DATE] VARCHAR(100), [DATA] VARCHAR(400), C3NULL BIT)
  
  DECLARE 
    @DATE VARCHAR(100), 
	@COLUMN1 VARCHAR(50),@COLUMN2 VARCHAR(50),@COLUMN3 VARCHAR(50)

DECLARE CURSOR1 CURSOR
FOR SELECT * FROM DUMMY;

OPEN CURSOR1;

FETCH NEXT FROM CURSOR1 INTO 
    @DATE, @COLUMN1,@COLUMN2,@COLUMN3;

WHILE @@FETCH_STATUS = 0
    BEGIN
		
		IF(@COLUMN3 IS NOT NULL)
		BEGIN
			 IF(NOT EXISTS(SELECT TOP 1 1 FROM TEMPDB..#TEMPTABLE1 WHERE [DATE]=@DATE AND C3NULL=1))
				INSERT INTO TEMPDB..#TEMPTABLE1 VALUES (@DATE,@COLUMN3,1)
			--ELSE
			--	UPDATE TEMPDB..#TEMPTABLE1 SET [DATA]=CONCAT([DATA],',',@COLUMN3)
			--		WHERE [DATE]=@DATE
            -- If any logic need write here
		END
		ELSE
			BEGIN
			
				IF(EXISTS(SELECT TOP 1 1 FROM TEMPDB..#TEMPTABLE1 WHERE [DATE]=@DATE AND C3NULL=0))
					UPDATE TEMPDB..#TEMPTABLE1 SET [DATA]=CONCAT([DATA],',',CONCAT(@COLUMN1,',',@COLUMN2))
					WHERE [DATE]=@DATE AND C3NULL=0

				IF(NOT EXISTS(SELECT TOP 1 1 FROM TEMPDB..#TEMPTABLE1 WHERE [DATE]=@DATE AND C3NULL=0))
					INSERT INTO TEMPDB..#TEMPTABLE1 VALUES (@DATE,CONCAT(@COLUMN1,',',@COLUMN2),0)

				
			END
        
        FETCH NEXT FROM CURSOR1 INTO 
             @DATE, @COLUMN1,@COLUMN2,@COLUMN3;
    END;

CLOSE CURSOR1;

DEALLOCATE CURSOR1;
SELECT [DATE],[DATA] FROM TEMPDB..#TEMPTABLE1

Brahma Prakash Shukla1y ago

SELECT 
    [Date],
    CASE 
        WHEN Column3 IS NOT NULL THEN Column3
        ELSE CONCAT_WS(', ', Column1, Column2)
    END AS Concate
FROM 
    YourTable
ORDER BY 
    [Date];

Debashish Mahonta1y ago
@Aman Gupta your query not working.
+1

Aman Gupta1y ago

Hi Debashish,

Below is the updated query.

SELECT
    CAST(DepartureDT AS DATE) AS TDate,
    EID AS EID,
    Name AS EName,
    Designation AS Designation,
    CAST(ArrivalDT AS DATE) AS ADate,
    CASE
        WHEN Other IS NULL THEN STRING_AGG(
            CASE
                WHEN Column3 IS NULL THEN CONCAT(Column1, ', ', Column2)
                ELSE Column3
            END, ', ')
        ELSE Other
    END WITHIN GROUP (ORDER BY DepartureDT) AS Concate
FROM
    Tours
GROUP BY
    CAST(DepartureDT AS DATE), EID, Name, Designation, CAST(ArrivalDT AS DATE);

Debashish Mahonta1y ago

What is error in my code? How to solve my code error?

SELECT
	CAST(DepartureDT AS DATE) as TDate,
	EID AS EID, Name AS EName, Designation AS Designation, CAST(ArrivalDT AS DATE) as ADate,
	CASE
        WHEN Other IS NULL THEN STRING_AGG(CONCAT(        
	CASE
		WHEN CAST(DepartureDT AS TIME) BETWEEN '06:00:00' AND '12:00:00' THEN 'Morning '
		WHEN CAST(DepartureDT AS TIME) BETWEEN '12:01:00' AND '04:00:00' THEN 'Noon '
		WHEN CAST(DepartureDT AS TIME) BETWEEN '04:01:00' AND '06:00:00' THEN 'Afternoon '
        WHEN CAST(DepartureDT AS TIME) BETWEEN '06:01:00' AND '08:00:00' THEN 'Evening '
		ELSE 'Night ' End +Format(DepartureDT ,'hh:mm')+ ' From ',' '+Departure,+' n‡Z iIbv w`‡q '+ 
	CASE
		WHEN CAST(ArrivalDT AS TIME) BETWEEN '06:00:00' AND '12:00:00' THEN 'Morning '
		WHEN CAST(ArrivalDT AS TIME) BETWEEN '12:01:00' AND '04:00:00' THEN 'Noon '
		WHEN CAST(ArrivalDT AS TIME) BETWEEN '04:01:00' AND '06:00:00' THEN 'Afternoon '
        WHEN CAST(ArrivalDT AS TIME) BETWEEN '06:01:00' AND '08:00:00' THEN 'Evening '
		ELSE 'Night ' End +Format(ArrivalDT ,'hh:mm')+ ' To ', Arrival),' ')
		ELSE Other End
	WITHIN GROUP (ORDER BY DepartureDT) AS TourDetails

FROM 
	Tours
GROUP BY 
	CAST(DepartureDT AS DATE), EID, Name, Designation, CAST(ArrivalDT AS DATE);

Jayraj Chhaya1y ago
To achieve the desired concatenation based on the specified conditions, you can use the SQL CASE statement along with the COALESCE function. Below is an example SQL query that demonstrates how to concatenate Column1 and Column2 when Column3 is NULL, and to return Column3 when it is not NULL.
```
SELECT 
    Date,
    CASE 
        WHEN Column3 IS NULL THEN CONCAT(Column1, ', ', Column2)
        ELSE Column3
    END AS Concate
FROM 
    YourDataTable
WHERE 
    Column1 IS NOT NULL OR Column2 IS NOT NULL OR Column3 IS NOT NULL;
```
This query checks each row: if Column3 is NULL, it concatenates Column1 and Column2 with a comma; otherwise, it returns the value of Column3. The WHERE clause ensures that only rows with at least one non-null value are included in the result. Adjust YourDataTable to the actual name of your data table.
+1