how to combine two table in t-sql?

Question

Hi everybody

I have a table [Order] there are many orders in it.

Now I want to show a monthly calendar in my project and it should show each order for a specific date in front of it.

I hava this code for monthly :

;WITH DateTable AS
(
 SELECT  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [date] 
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= EOMONTH(GETDATE())
)
 SELECT CAST([date] AS date) DaysOfMonth --, o.*
 FROM DateTable as d

It works fine.

I left join this table with my orders table but each same date shows in a separate row.

I want this kind of result:

how can I do that?

Answer 1

Hi @Farshad Valizade

Here's a query, which is based on up to three orders per day. If the maximum number of orders per day exceeds three, then you need to modify it accordingly.

CREATE TABLE #ORDER (Order_id INT, [Date] DATE,[Time] TIME)
INSERT INTO #ORDER VALUES
(1001,'2024-10-01','08:00'),
(1002,'2024-10-01','10:00'),
(1003,'2024-10-01','12:00'),
(1004,'2024-10-03','07:00'),
(1005,'2024-10-04','16:00'),
(1006,'2024-10-04','20:00')

;WITH DateTable AS
(
 SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [date] 
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= EOMONTH(GETDATE())
),OrderTable AS
(
 SELECT CAST(D.[date] AS date) DaysOfMonth
       ,CONCAT(Order_id,'-',LEFT([Time],5)) AS OrderID_Time
	   ,CONCAT('Order',CAST(ROW_NUMBER()OVER(PARTITION BY O.Date ORDER BY Order_id) AS VARCHAR(10))) AS OrderNum
 FROM DateTable AS D LEFT JOIN #ORDER AS O ON D.date=O.Date
)
 SELECT DaysOfMonth,[Order1],[Order2],[Order3]
 FROM OrderTable
 PIVOT(MAX(OrderID_Time) FOR OrderNum IN ([Order1],[Order2],[Order3]))P

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

This requirment could be resolved by PIVOT. Since the count of columns is dynamic, it has to use dynamic SQL.

The first step just like you thought, JOIN the DateTable and Order first, then the steps following:

1. Build a PIVOT table named with D, which fill order_id in each row of date.
2. Build another PIVOT table named with T, which fill time in each row of date.
3. Join D and T on their date, concat order_id and time like the format as you shown.
4. Convert the SQL to a dynamic SQL:
   1. Get the max count of orders in a day.
   2. Build a string for PIVOT columns and assign to variant p_cols.
   3. Build a string for output columns and assign to variant o_cols.
   4. Replace 3 parts of SQL with those 2 variants.
5. EXECUTE the SQL

Code is here:

--Create a test table of order
CREATE TABLE [#Order]
(
	order_id varchar(4) primary key,
	[date] date,
	[time] time
)
GO

INSERT INTO [#Order]
Values
 ('1001', '2024/10/01', '08:00')
,('1002', '2024/10/01', '10:00')
,('1003', '2024/10/01', '12:00')
,('1004', '2024/10/03', '07:00')
,('1005', '2024/10/04', '16:00')
,('1006', '2024/10/04', '20:00')
,('1007', '2024/10/01', '14:00')
GO

DECLARE @max_cols INT                  --Max count of orders in a day
DECLARE @p_cols NVARCHAR(MAX) = N''    --string of pivot columns
DECLARE @o_cols NVARCHAR(MAX) = N''    --string of output columns
DECLARE @n INT
DECLARE @c_n NVARCHAR(1)
DECLARE @SQL NVARCHAR(MAX) = N''       --SQL to execute

--Get the max count of oders in a day
SELECT TOP(1) @max_cols = COUNT(order_id) FROM [#Order] GROUP BY [date] ORDER BY COUNT(order_id) DESC

--build p_cols and o_cols
SET @n = 1
WHILE (@n <= @max_cols)
BEGIN
	SET @c_n = CAST(@n AS NVARCHAR)
	SET @p_cols += N'[' + @c_n + N']'
	SET @o_cols += (N'ISNULL(D.[' + @c_n + '] + ''-'' + T.[' + @c_n + '], N'''') AS [Order ' + @c_n + ']')
	IF @n < @max_cols 
	BEGIN
		SET @p_cols += N', '
		SET @o_cols += N', '
	END
	SET @n += 1
END

--debug output
--PRINT @p_cols
--PRINT @o_cols

--Build SQL string
SET @SQL = 
'WITH DateTable AS
(
 SELECT  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [date] 
 UNION ALL
 SELECT DATEADD(DAY, 1, [date])
 FROM DateTable
 WHERE DATEADD(DAY, 1, [date]) <= EOMONTH(GETDATE())
)
SELECT D.DaysOfMonth, '
+
@o_cols 
+' FROM
(
	SELECT 
		CAST(d.[date] AS date) DaysOfMonth
		, O.order_id
		, ROW_NUMBER() OVER(PARTITION BY O.[date] ORDER BY O.[time]) AS SN
	FROM DateTable as d
	LEFT JOIN [#Order] O ON d.[date] = O.[date]  
)S
PIVOT
(
	MAX(order_id) FOR SN IN ('
+
@p_cols
+')
)D
INNER JOIN
(
	SELECT * FROM
	(
		SELECT 
			CAST(d.[date] AS date) DaysOfMonth
			, FORMAT(O.[time], N''hh\:mm'') AS [Time]
			, ROW_NUMBER() OVER(PARTITION BY O.[date] ORDER BY O.[time]) AS SN
		FROM DateTable as d
		LEFT JOIN [#Order] O ON d.[date] = O.[date]  
	)S
	PIVOT
	(
		MAX([Time]) FOR SN IN ('
+
@p_cols
+')
	)TT
)T
ON D.DaysOfMonth = T.DaysOfMonth
'

--debug output
--PRINT @SQL

--Execute dynamic SQL to get result
EXECUTE(@SQL)

-------------------------------------------------------------------------------------------
--SQL pattern to debug, after get correct result, copy to assign @SQL, replace output column list and 2 PIVOT column lists with variants
--WITH DateTable AS
--(
-- SELECT  DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [date] 
-- UNION ALL
-- SELECT DATEADD(DAY, 1, [date])
-- FROM DateTable
-- WHERE DATEADD(DAY, 1, [date]) <= EOMONTH(GETDATE())
--)
--SELECT D.DaysOfMonth, 
--ISNULL(D.[1] + '-' + T.[1], N'') AS [Order 1], ISNULL(D.[2] + '-' + T.[2], N'') AS [Order 2], ISNULL(D.[3] + '-' + T.[3], N'') AS [Order 3]
-- FROM
--(
--	SELECT 
--		CAST(d.[date] AS date) DaysOfMonth
--		, O.order_id
--		, ROW_NUMBER() OVER(PARTITION BY O.[date] ORDER BY O.[time]) AS SN
--	FROM DateTable as d
--	LEFT JOIN [#Order] O ON d.[date] = O.[date]  
--)S
--PIVOT
--(
--	MAX(order_id) FOR SN IN (
--[1],[2],[3]
--)
--)D
--INNER JOIN
--(
--	SELECT * FROM
--	(
--		SELECT 
--			CAST(d.[date] AS date) DaysOfMonth
--			, FORMAT(O.[time], N'hh\:mm') AS [Time]
--			, ROW_NUMBER() OVER(PARTITION BY O.[date] ORDER BY O.[time]) AS SN
--		FROM DateTable as d
--		LEFT JOIN [#Order] O ON d.[date] = O.[date]  
--	)S
--	PIVOT
--	(
--		MAX([Time]) FOR SN IN (
--[1],[2],[3]
--)
--	)TT
--)T
--ON D.DaysOfMonth = T.DaysOfMonth

DROP TABLE [#Order]