July 2, 2013 at 8:21 am
I used code below to create cross tab query. It works fine.
How to add code to get total of months, like Year to Day?
SELECT *
FROM (
SELECT
year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr,
may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot
July 2, 2013 at 10:48 am
July 2, 2013 at 10:57 am
I found the code here but no test data only screen shot.
http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
July 2, 2013 at 11:01 am
This is quick and dirty, but since the number of months in a year isn't going to change any time soon you can do this:
;With Invoice AS
(
SELECT '20130101' [InvoiceDate], 100 [InvoiceAmount] UNION ALL
SELECT '20120102', 101 UNION ALL
SELECT '20120202', 140 UNION ALL
SELECT '20120302', 130 UNION ALL
SELECT '20120402', 120 UNION ALL
SELECT '20120502', 110 UNION ALL
SELECT '20130102', 101 UNION ALL
SELECT '20130202', 140 UNION ALL
SELECT '20130302', 130 UNION ALL
SELECT '20130402', 120 UNION ALL
SELECT '20130502', 110 UNION ALL
SELECT '20130502', 111
)
SELECT *,ISNULL(jan,0) +
ISNULL(feb,0) +
ISNULL(mar,0) +
ISNULL(apr,0) +
ISNULL(may,0) +
ISNULL(jun,0) +
ISNULL(jul,0) +
ISNULL(aug,0) +
ISNULL(sep,0) +
ISNULL(oct,0) +
ISNULL(nov,0) +
ISNULL(dec,0) [YearToDate]
FROM (
SELECT
year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS [pivot]
July 2, 2013 at 12:24 pm
It is working great.
One more question, I want to insert the result into a temp table but code below did not work.
Something wrong with my code?
select * into #myinvoice from ( //your code below
SELECT *,ISNULL(jan,0) +
ISNULL(feb,0) +
ISNULL(mar,0) +
ISNULL(apr,0) +
ISNULL(may,0) +
ISNULL(jun,0) +
ISNULL(jul,0) +
ISNULL(aug,0) +
ISNULL(sep,0) +
ISNULL(oct,0) +
ISNULL(nov,0) +
ISNULL(dec,0) [YearToDate]
FROM (
SELECT
year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS [pivot]
)
July 2, 2013 at 12:34 pm
adonetok (7/2/2013)
It is working great.One more question, I want to insert the result into a temp table but code below did not work.
Something wrong with my code?
select * into #myinvoice from ( //your code below
SELECT *,ISNULL(jan,0) +
ISNULL(feb,0) +
ISNULL(mar,0) +
ISNULL(apr,0) +
ISNULL(may,0) +
ISNULL(jun,0) +
ISNULL(jul,0) +
ISNULL(aug,0) +
ISNULL(sep,0) +
ISNULL(oct,0) +
ISNULL(nov,0) +
ISNULL(dec,0) [YearToDate]
FROM (
SELECT
year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS [pivot]
)
You need to give you table an alias OR just do this as an insert.
select * into #myinvoice from ( //your code below
SELECT *,ISNULL(jan,0) +
ISNULL(feb,0) +
ISNULL(mar,0) +
ISNULL(apr,0) +
ISNULL(may,0) +
ISNULL(jun,0) +
ISNULL(jul,0) +
ISNULL(aug,0) +
ISNULL(sep,0) +
ISNULL(oct,0) +
ISNULL(nov,0) +
ISNULL(dec,0) [YearToDate]
FROM (
SELECT
year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS [pivot]
) as MYALIAS
Or the simpler version....
INSERT into #myinvoice
SELECT *,ISNULL(jan,0) +
ISNULL(feb,0) +
ISNULL(mar,0) +
ISNULL(apr,0) +
ISNULL(may,0) +
ISNULL(jun,0) +
ISNULL(jul,0) +
ISNULL(aug,0) +
ISNULL(sep,0) +
ISNULL(oct,0) +
ISNULL(nov,0) +
ISNULL(dec,0) [YearToDate]
FROM (
SELECT
year(CONVERT(DATE,InvoiceDate)) as [year],left(datename(month,CONVERT(DATE,InvoiceDate)),3)as [month],
InvoiceAmount as Amount
FROM Invoice
) as s
PIVOT
(
SUM(Amount)
FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
)AS [pivot]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2013 at 12:42 pm
Thank you very much. Now all is working.
The key is "You need to give you table an alias"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply