May 14, 2010 at 6:28 am
Hi,
For reporting purpose I need to conglomerate set of data rows in to columns. I wonder if someone and provide me optimal select query to perform.
I've dataset in following format
SalesPart WCDateDelivery Date Qty
883/575/01321/03/201021-MAR-10 00.00.00.0000004147
883/575/01321/03/201022-MAR-10 00.00.00.0000001963
883/575/01321/03/201023-MAR-10 00.00.00.0000002639
883/575/01321/03/201024-MAR-10 00.00.00.0000004498
883/575/01321/03/201025-MAR-10 00.00.00.0000003523
883/575/01321/03/201027-MAR-10 00.00.00.0000006721
I wonder how I could convert it into following format using Select query
SalesPart WCDateSunday MondayTuesday Wednesday Thrusday Friday Saturday
883/575/01321/03/20104147 19632639 4498 3523 0 6721
Bhavesh
.NET and SQL Server Blog
May 14, 2010 at 7:09 am
PIVOT might do the trick for you:
DECLARE @test-2 TABLE (
SalesPart char(11),
WCDate datetime,
[Delivery Date] datetime,
Qtyint
)
INSERT INTO @test-2
SELECT '883/575/013',CONVERT(datetime,'21/03/2010',103),CONVERT(datetime,'21-MAR-10 00:00:00.000',113),4147 UNION ALL
SELECT '883/575/013',CONVERT(datetime,'21/03/2010',103),CONVERT(datetime,'22-MAR-10 00:00:00.000',113),1963 UNION ALL
SELECT '883/575/013',CONVERT(datetime,'21/03/2010',103),CONVERT(datetime,'23-MAR-10 00:00:00.000',113),2639 UNION ALL
SELECT '883/575/013',CONVERT(datetime,'21/03/2010',103),CONVERT(datetime,'24-MAR-10 00:00:00.000',113),4498 UNION ALL
SELECT '883/575/013',CONVERT(datetime,'21/03/2010',103),CONVERT(datetime,'25-MAR-10 00:00:00.000',113),3523 UNION ALL
SELECT '883/575/013',CONVERT(datetime,'21/03/2010',103),CONVERT(datetime,'27-MAR-10 00:00:00.000',113),6721
SELECT SalesPart, WCDate, Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday
FROM (
SELECT SalesPart, WCDate, datename(weekday ,[Delivery Date]) AS weekday, Qty
FROM @test-2
) AS T
PIVOT (SUM(Qty) FOR weekday in ([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Saturday])) AS P
Hope this helps
Gianluca
-- Gianluca Sartori
May 14, 2010 at 7:12 am
As a side note, next time you ask for help, try providing a table script and sample data as I did in my reply.
You will find more people willing to help if they jus have to copy and paste the code in SSMS to have sample data.
Take a look at the article linked in my signature, Jeff says it better than I ever could.
-- Gianluca Sartori
May 14, 2010 at 9:26 am
Thanks for your quick reply
Bhavesh
.NET and SQL Server Blog
May 15, 2010 at 7:13 am
Not to take anything at all away from Gianluca's fine implementation, here's an alternative using CASE:
SELECT T.SalesPart,
T.WCDate,
SUM(CASE WHEN CA.week_day = 'Sunday' THEN T.Qty ELSE 0 END) AS Sunday,
SUM(CASE WHEN CA.week_day = 'Monday' THEN T.Qty ELSE 0 END) AS Monday,
SUM(CASE WHEN CA.week_day = 'Tuesday' THEN T.Qty ELSE 0 END) AS Tuesday,
SUM(CASE WHEN CA.week_day = 'Wednesday' THEN T.Qty ELSE 0 END) AS Wednesday,
SUM(CASE WHEN CA.week_day = 'Thursday' THEN T.Qty ELSE 0 END) AS Thursday,
SUM(CASE WHEN CA.week_day = 'Friday' THEN T.Qty ELSE 0 END) AS Friday,
SUM(CASE WHEN CA.week_day = 'Saturday' THEN T.Qty ELSE 0 END) AS Saturday
FROM @test-2 T
CROSS APPLY (SELECT DATENAME(WEEKDAY, T.[Delivery Date])) CA (week_day)
GROUP BY
T.SalesPart,
T.WCDate;
I have a conceptual problem with PIVOT - unlike UNPIVOT which is genuinely useful and adds something new to T-SQL. PIVOT is limited to a single aggregation (although it is possible to code around that) and performs slightly less well than the equivalent CASE expressions.
Paul
May 17, 2010 at 1:11 am
Thanks for chiming in, Paul.
Your input is always welcome, especially when brings in something new!
I didn't know that syntax to specify column names for CROSS APPLY, thanks for sharing.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply