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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy