In Summary: I basically wanted to take the data in a table, then linked data from another table concatenated into a comma separated string, and attached as a varchar column on the end of the first table!
I have the mis-fortune of working on a terrible intranet system based on technology built by a company I won’t actually name- so for the purpose of this post, lets say they are called Source. It’s basically a big buggy CMS powered by some aging and creaky classic asp files and lashing of very frail javascript (this isn’t a legacy system- it was installed in the company around a year ago!). It allows you to build forms and data grids, but is quite restrictive with how you attach these to the database- you cannot for example use a stored procedure as a data source- it has to be a table or a view.
We have an internal app to allow staff to order with a table structure like so;
OrderHeader -> OrderDetail -> Publisher -> Warehouse
On the order header view page we need to know which warehouses are involved, which means a query going right down the chain from product on the order line, to the publisher, down to which warehouse that publisher is based at- because we can only tie to a single table or view I had a problem unless i wanted loads of repeated rows- after some googling I found CROSS to be what I was after;
Based on the above post I was able to build SQL like so;
SELECT
OrderHeader.id,
OrderHeader.user_id,
OrderHeader.OrderDate,
Users.name,
WareHouses = LEFT(o.list, LEN(o.list)-1)
FROM
OrderHeader INNER JOIN
User ON OrderHeader.UserId = User.UserId LEFT OUTER JOIN
CROSS APPLY
(
SELECT distinct
CONVERT(VARCHAR(50), WareHouse.Name) + ',' AS [text()]
FROM
WareHouse
INNER JOIN Publishers ON WareHouse.id = Publishers.WarehouseId
INNER JOIN OrderDetailLine ON Publishers.PublisherID = OrderDetailLine.PublisherID
WHERE
OrderDetailLine.OrderHeaderId = OrderHeader.OrderHeaderId
ORDER BY
CONVERT(VARCHAR(50), WareHouse.Name) + ','
FOR XML PATH('')
) o (list)
This bought me back each row, with a comma separated list of the warehouse names in the end column which i could then filter on!
Related posts:
