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

Safe Travels in SQL!

No Comments