November 10, 2011 at 7:46 am
I have an existing Pivot Table in an Excel Worrksheet and I need to duplicate in SQL Server.
The Count of Sales and Sum of Sales are broken down by Type, Ho-A, TYPE-BT, etc and then there is a Total Count and Sum regardless of Type as listed below.
Can anyone please tell me how I could do this?
Thank you.
HO-ATYPE-BTTYPE-CBTYPE-TDPTYPE-HOTotal No Of SalesTotal Sum Sales
Date IssuedNo Of SalesSum SalesNo Of SalesSum SalesNo Of SalesSum SalesNo Of SalesSum SalesNo Of SalesSum Sales
11/8/201187,806 1534 54,773 2227,893 3641,006
11/7/20111817,798 1834 86,269 1316,565 4041,466
11/5/201122,017 21,278 43,602 86,897
11/4/20111316,568 1537 44,155 1719,340 3540,600
11/3/20111313,828 1585 54,828 2126,295 4045,536
11/2/20111012,590 2906 1430 88,337 2634,982 4757,245
11/1/20111515,904 21,124 66,143 2326,483 4649,654
10/31/20112220,611 66,823 1923,083 4750,517
10/29/201144,203 22,309 66,512
10/28/20111920,725 1642 1111,399 2930,372 6063,138
10/27/20112222,312 1430 97,732 1924,188 5154,662
10/26/20111415,052 1430 43,202 2429,645 4348,329
10/25/20111516,734 1665 65,948 2125,693 4349,040
10/24/20111316,832 1430 33,017 2429,425 4149,704
10/22/201121,762 21,375 55,310 98,447
10/21/20111919,799 1715,793 3235,969 6871,561
10/20/20111414,859 21,129 75,949 1719,777 4041,714
10/19/20111312,988 910,879 1922,664 4146,531
10/18/20111917,206 1430 43,690 2629,328 5050,654
10/17/20111215,046 1562 32,405 1617,895 3235,908
10/15/201122,255 1555 1730 55,585 99,125
10/14/20111614,667 1440 1111,074 2125,360 4951,541
10/13/20111216,216 31,335 55,555 1823,984 3847,090
10/12/20111817,488 89,871 1824,058 4451,417
10/11/20111213,786 54,894 1920,989 3639,669
10/10/201197,865 1658 89,285 1820,540 3638,348
10/8/201134,223 1696 33,656 78,575
10/7/20112425,315 1430 1554 32,152 2535,924 5464,375
Date IssuedNo Of SalesForm NameSales
11/9/201122HO-327088
11/9/201110HO-A13164
11/9/20111HO-BT430
11/9/20118TDP-18053
11/8/201122HO-327893
11/8/20118HO-A7806
11/8/20111HO-CON-B534
11/8/20115TDP-14773
11/7/201113HO-316565
11/7/201118HO-A17798
11/7/20111HO-CON-B834
11/7/20118TDP-16269
11/5/20114HO-33602
11/5/20112HO-A2017
11/5/20112TDP-11278
11/4/201117HO-319340
11/4/201113HO-A16568
11/4/20111HO-BT537
11/4/20114TDP-14155
11/3/201121HO-326295
Date IssuedNo Of SalesForm NameSales
11/9/201122HO-327088
11/9/201110HO-A13164
11/9/20111HO-BT430
11/9/20118TDP-18053
11/8/201122HO-327893
11/8/20118HO-A7806
11/8/20111HO-CON-B534
11/8/20115TDP-14773
11/7/201113HO-316565
11/7/201118HO-A17798
11/7/20111HO-CON-B834
11/7/20118TDP-16269
11/5/20114HO-33602
11/5/20112HO-A2017
11/5/20112TDP-11278
11/4/201117HO-319340
11/4/201113HO-A16568
11/4/20111HO-BT537
11/4/20114TDP-14155
11/3/201121HO-326295
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 10, 2011 at 8:26 am
You're much better off doing this in a standard SQL Query than using SSIS.
Try something like this:
SELECT
DateIssued,
COUNT(CASE WHEN [Type] = 'HO-A' THEN [NumSales] ELSE NULL END) AS [HO-A Num Sales],
SUM(CASE WHEN [Type] = 'HO-A' THEN [SaleValue] ELSE NULL END) AS [HO-A Sum Sales],
COUNT(CASE WHEN [Type] = 'TYPE-BT' THEN [NumSales] ELSE NULL END) AS [TYPE-BT Num Sales],
SUM(CASE WHEN [Type] = 'TYPE-BT' THEN [SaleValue] ELSE NULL END) AS [TYPE-BT Sum Sales],
COUNT(CASE WHEN [Type] = 'TYPE-CB' THEN [NumSales] ELSE NULL END) AS [TYPE-CB Num Sales],
SUM(CASE WHEN [Type] = 'TYPE-CB' THEN [SaleValue] ELSE NULL END) AS [TYPE-CB Sum Sales],
COUNT(CASE WHEN [Type] = 'TYPE-TDP' THEN [NumSales] ELSE NULL END) AS [TYPE-TDP Num Sales],
SUM(CASE WHEN [Type] = 'TYPE-TDP' THEN [SaleValue] ELSE NULL END) AS [TYPE-TDP Sum Sales],
COUNT(CASE WHEN [Type] = 'TYPE-HO' THEN [NumSales] ELSE NULL END) AS [TYPE-HO Num Sales],
SUM(CASE WHEN [Type] = 'TYPE-HO' THEN [SaleValue] ELSE NULL END) AS [TYPE-HO Sum Sales],
COUNT([NumSales]) AS [Total Num Sales],
SUM([SaleValue]) AS [Total Sum Sales]
FROM [YourTable]
GROUP BY DateIssued
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply