Collapse Rows of Dates into a Single, Comma-Delimited Column in SQL Server
The Internet is an amazing thing. I remember back in the early days of my career, when you hit a problem the only resources at your disposal were your colleages and documentation. Today you simply do a Google search and often wind up on StackOverflow. That's how I found the solution the problem I faced last night. Here's the StackOverflow that got me over the hump:
https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string/545672
Thanks to Ritesh and P5Coder, who ever you are!
To demonstrate the problem and solution I came up with a simple Order example which is a familiar example to many developers. The issue is that I'd like to show the order history dates along with a single amount for the order. Hopefully someone else will benefit from the power of the internet, like I did:
Setup
Create the Order and Order History tables:
CREATE TABLE [dbo].[Orders]( [OrderId] [char](8) NOT NULL, [Amount] [money] NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[OrderHistory]( [OrderHistoryId] [int] IDENTITY(1,1) NOT NULL, [OrderId] [char](8) NOT NULL, [OrderDate] [date] NULL, CONSTRAINT [PK_OrderHistory] PRIMARY KEY CLUSTERED ( [OrderHistoryId] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Populate some sample data:
SET IDENTITY_INSERT [dbo].[OrderHistory] ON INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (1, N'A2323231', CAST(N'2016-01-23' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (2, N'A2323231', CAST(N'2016-01-22' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (3, N'A2323231', CAST(N'2016-01-21' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (4, N'A2323232', CAST(N'2016-01-23' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (5, N'A2323232', CAST(N'2016-01-22' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (6, N'A2323232', CAST(N'2016-01-21' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (7, N'B1231232', CAST(N'2016-01-18' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (8, N'B1231232', CAST(N'2016-01-17' AS Date)) INSERT [dbo].[OrderHistory] ([OrderHistoryId], [OrderId], [OrderDate]) VALUES (9, N'B1231232', CAST(N'2016-01-15' AS Date)) SET IDENTITY_INSERT [dbo].[OrderHistory] OFF GO INSERT [dbo].[Orders] ([OrderId], [Amount]) VALUES (N'A2323231', 250.0000) INSERT [dbo].[Orders] ([OrderId], [Amount]) VALUES (N'A2323232', 350.0000) INSERT [dbo].[Orders] ([OrderId], [Amount]) VALUES (N'B1231232', 790.0000) GO
Problem
I have a report that I need to flatten out a list of dates, which were causing duplicate values in the report. For example the following JOIN:
SELECT Orders.OrderId, OrderHistory.OrderDate, Orders.Amount FROM Orders INNER JOIN OrderHistory ON Orders.OrderId = OrderHistory.OrderId
Causes the following output (notice the amounts are per order and duplicated)
OrderId | OrderDate | Amount -------- ---------- ------ A2323231 | 2016-01-23 | 250.00 A2323231 | 2016-01-22 | 250.00 A2323231 | 2016-01-21 | 250.00 A2323232 | 2016-01-23 | 350.00 A2323232 | 2016-01-22 | 350.00 A2323232 | 2016-01-21 | 350.00 B1231232 | 2016-01-18 | 790.00 B1231232 | 2016-01-17 | 790.00 B1231232 | 2016-01-15 | 790.00
Solution
Using FOR XML we can JOIN the rows with the same OrderId and concatenate the dates into a comma-delimited string:
SELECT DISTINCT HIST2.OrderId, SUBSTRING( ( SELECT CONCAT(', ', FORMAT(HIST1.OrderDate, 'MM/dd/yyyy')) FROM OrderHistory HIST1 WHERE HIST1.OrderId = HIST2.OrderId ORDER BY OrderDate FOR XML PATH ('') ), 3, 5000) OrderDates FROM OrderHistory HIST2
This produces the desired, "flat" output with the Order History dates collapsed into a single column per order:
OrderId | OrderDates --------- ----------------------------------- A2323231 | 01/21/2016, 01/22/2016, 01/23/2016 A2323232 | 01/21/2016, 01/22/2016, 01/23/2016 B1231232 | 01/15/2016, 01/17/2016, 01/18/2016