CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Makdaddy
Makdaddy

Reputation: 39

Return Multiple Dates Horizontally in SQL

purchline has 

itemid, deliverydate, qtyordered

select * from purchline
itemid   deliverydate    qtyordered
74700    01/01/2000      50
74700    01/01/2007      51
74700    01/01/2010      5

Q.

I would prefer to see this as a stored procedure and look as follows:

itemid  deliverydates                     TotalOrdered 
74700   01/01/2000 01/01/2007 01/01/2010  106

Upvotes: 0

Views: 154

Answers (2)

Kevin Aenmey
Kevin Aenmey

Reputation: 13429

Edited for SQL Server 2005

If you are using SQL Server:

SELECT itemid
    ,deliverydates = STUFF((SELECT ' ' + CONVERT(NVARCHAR, deliverydate, 101)
                           FROM purchline b 
                           WHERE b.itemid = a.itemid
                           FOR XML PATH('')), 1, 1, '')
    ,TotalOrdered = SUM(qtyordered) 
FROM purchline a
GROUP BY itemid

Test with the following:

-- Setup test data
DECLARE @T1 AS TABLE (
    itemid NVARCHAR(50)
    ,deliverydate DATETIME
    ,qtyordered INT);

INSERT @T1 VALUES ('74700', '20000101', 50)
INSERT @T1 VALUES ('74700', '20070101', 51)
INSERT @T1 VALUES ('74700', '20100101', 5)

-- Return the desired results
SELECT itemid
    ,deliverydates = STUFF((SELECT ' ' + CONVERT(NVARCHAR, deliverydate, 101)
                           FROM @T1 b 
                           WHERE b.itemid = a.itemid
                           FOR XML PATH('')), 1, 1, '')
    ,TotalOrdered = SUM(qtyordered) 
FROM @T1 a
GROUP BY itemid

Upvotes: 1

Ambrose
Ambrose

Reputation: 511

If you are using MySQL:

SELECT   ItemID, 
         GROUP_CONCAT(deliverydate SEPARATOR ' ') as deliverydate,
         SUM(qtyordered) qtyordered
FROM     purchline
GROUP BY ItemID

Upvotes: 1

Related Questions