how to pivot columns

Question

question

vanapandi asked Mar 15, '13

how to pivot columns

i want to pivot.. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[sales_report]( [salesid] [bigint] NOT NULL, [productName] [varchar](100) NULL, [Qty] [bigint] NULL, [Date] [date] NULL, CONSTRAINT [PK_sales_report] PRIMARY KEY CLUSTERED ( [salesid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (1, N'dettol', 6, CAST(0xDD360B00 AS Date)) INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (2, N'hamam', 10, CAST(0xDE360B00 AS Date)) INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (3, N'sweet', 10, CAST(0xDF360B00 AS Date)) INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (4, N'test', 23, CAST(0xDC360B00 AS Date)) INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (5, N'boost', 10, CAST(0xDF360B00 AS Date)) INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (6, N'horlicks', 2, CAST(0xDD360B00 AS Date)) INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (7, N'shampo', 5, CAST(0xDE360B00 AS Date)) i need result like below ProductName 14 15 16 17 Total --------------------------------- dettol 0 6 0 0 6 hamam 0 0 10 0 10 boost 0 0 0 10 10 horlicks 0 2 0 0 2 shampo 0 0 5 0 5 sweet 0 0 0 10 10 test 23 0 0 0 23

sql-server-2008 sql-server-2005 pivot query-results

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-03-15T11:05:02Z

tanglesoft answered Mar 15, '13

New version. SELECT ProductName, ISNULL([14],0) [14], ISNULL([15],0) [15], ISNULL([16],0) [16], ISNULL([17],0) [17], ISNULL([14],0) + ISNULL([15],0) + ISNULL([16],0) + ISNULL([17],0) Total FROM ( SELECT ProductName, DATEPART(DAY, [Date]) [Day], Qty FROM sales_report) AS SourceTable PIVOT ( SUM(Qty) FOR [Day] IN ([14], [15], [16], [17]) ) AS PivotTable; Never worked out how to use pivot with variable dates without resorting to dynamic SQL. Also total calculations is a bit crap.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vanapandi commented · Mar 15, 2013 at 11:23 AM

hi...i got it...very nice and simple...Is this possible for 25-02-2013 to 25-03-2013?I want sales details between these days..

0 ·

tanglesoft commented · Mar 15, 2013 at 11:26 AM

Do you want daily totals between these dates or a total for the whole period

0 ·

vanapandi commented · Mar 15, 2013 at 11:29 AM

In header i want start with 25-02-2013 instead of 14-02-2013 and end with next month 25-03-2013.and i want daily and end of column should be total Qty

0 ·

Answer 2 · 2013-03-15T10:59:52Z

tanglesoft answered Mar 15, '13

Got this far SELECT ProductName, [14], [15], [16], [17] FROM ( SELECT ProductName, DATEPART(DAY, [Date]) [Day], Qty FROM sales_report) AS SourceTable PIVOT ( SUM(Qty) FOR [Day] IN ([14], [15], [16], [17]) ) AS PivotTable; Need to work out how to add total column

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2013-03-15T11:48:11Z

tanglesoft answered Mar 15, '13

SELECT * FROM dbo.sales_report /* ProductName 14 15 16 17 Total dettol 0 6 0 0 6 hamam 0 0 10 0 10 boost 0 0 0 10 10 horlicks 0 2 0 0 2 shampo 0 0 5 0 5 sweet 0 0 0 10 10 test 23 0 0 0 23 */ SELECT ProductName, ISNULL([25],0) [25], ISNULL([26],0) [26], ISNULL([27],0) [27], ISNULL([28],0) [28], ISNULL([1],0) [1], ISNULL([2],0) [2], ISNULL([3],0) [3], ISNULL([4],0) [4], ISNULL([5],0) [5], ISNULL([6],0) [6], ISNULL([7],0) [7], ISNULL([8],0) [8], ISNULL([9],0) [9], ISNULL([10],0) [10], ISNULL([11],0) [11], ISNULL([12],0) [12], ISNULL([13],0) [13], ISNULL([14],0) [14], ISNULL([15],0) [15], ISNULL([16],0) [16], ISNULL([17],0) [17], ISNULL([18],0) [18], ISNULL([19],0) [19], ISNULL([20],0) [20], ISNULL([21],0) [21], ISNULL([22],0) [22], ISNULL([23],0) [23], ISNULL([24],0) [24], ISNULL([25],0) + ISNULL([26],0) + ISNULL([27],0) + ISNULL([28],0) + ISNULL([1],0) + ISNULL([2],0) + ISNULL([3],0) + ISNULL([4],0) + ISNULL([5],0) + ISNULL([6],0) + ISNULL([7],0) + ISNULL([8],0) + ISNULL([9],0) + ISNULL([10],0) + ISNULL([11],0) + ISNULL([12],0) + ISNULL([13],0) + ISNULL([14],0) + ISNULL([15],0) + ISNULL([16],0) + ISNULL([17],0) + ISNULL([18],0) + ISNULL([19],0) + ISNULL([20],0) + ISNULL([21],0) + ISNULL([22],0) + ISNULL([23],0) + ISNULL([24],0) FROM ( SELECT ProductName, DATEPART(DAY, [Date]) [Day], Qty FROM sales_report) AS SourceTable PIVOT ( SUM(Qty) FOR [Day] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28]) ) AS PivotTable;

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vanapandi commented · Mar 15, 2013 at 12:30 PM

here , i got new problem while executing previous query..that is i got product Bread ,it has 25-02-103 Qty=12 and 25-03-2013 Qty=12..but in header 25 it will sum 12+12. here..Data INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (9, N'bread', 12, CAST(0xCB360B00 AS Date)) INSERT [dbo].[sales_report] ([salesid], [productName], [Qty], [Date]) VALUES (10, N'bread', 12, CAST(0xE7360B00 AS Date)) execute with previous query

0 ·

vanapandi commented · Mar 15, 2013 at 12:40 PM

i got another doubt..if i have date like 20-01-2012, 20-02-2013,20-03-2013 here how can i differentiate...actually i need like Jan-25-2013 to Feb-24-2013. month will be dynamically will change.i need sales details between 2 months 25 to 24.

0 ·

question