August 30, 2011 at 9:14 am
The following query works, but I need the result column Amt to be formatted with commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 1,000.00. This can be done normally with the statement CONVERT(VARCHAR(100), Amt, 1) when Amt is of type MONEY. However, SQL won't allow this in the SELECT column because the return value cannot be use in the aggregate SUM, nor can I put it in the PIVOT declaration.
Does anyone know how I might achieve this result? Thanks in advance for your help.
DECLARE @Budget TABLE
(
ID INT NOT NULL,
Amt MONEY NOT NULL,
Dt SMALLDATETIME NOT NULL
)
INSERT INTO @Budget (ID, Amt, Dt) SELECT 0, 1000.00, '08/30/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 1, 1001.00, '08/31/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 1, 1002.00, '08/31/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 2, 1003.00, '09/01/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 2, 1004.00, '09/01/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 3, 1005.00, '09/02/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 3, 1006.00, '09/02/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 4, 1007.00, '09/03/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 4, 1008.00, '09/03/2011'
--SELECT TOP 100 * FROM @Budget
SELECT *
FROM (SELECT ID, Amt, CONVERT(VARCHAR(10), Dt, 101) AS Dt
FROM @Budget) AS B
PIVOT ( SUM(Amt)
FOR Dt IN ( [08/30/2011],
[08/31/2011],
[09/01/2011],
[09/02/2011],
[09/03/2011])) as P
August 30, 2011 at 9:18 am
Can you format it in the front end? That is the preferred location to handle formatting.
_______________________________________________________________
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/
August 30, 2011 at 9:19 am
I second what Sean says, formatting is to done at the front end.
But, doesnt this work for you ( or am i missing the obvious ?? )
DECLARE @Budget TABLE
(
ID INT NOT NULL,
Amt MONEY NOT NULL,
Dt SMALLDATETIME NOT NULL
)
INSERT INTO @Budget (ID, Amt, Dt) SELECT 0, 1000.00, '08/30/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 1, 1001.00, '08/31/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 1, 1002.00, '08/31/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 2, 1003.00, '09/01/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 2, 1004.00, '09/01/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 3, 1005.00, '09/02/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 3, 1006.00, '09/02/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 4, 1007.00, '09/03/2011'
INSERT INTO @Budget (ID, Amt, Dt) SELECT 4, 1008.00, '09/03/2011'
--SELECT TOP 100 * FROM @Budget
SELECT *, CONVERT (VARCHAR(30), [08/31/2011] , 1) [08/31/2011]
FROM (SELECT ID, Amt, CONVERT(VARCHAR(10), Dt, 101) AS Dt
FROM @Budget) AS B
PIVOT ( SUM(Amt)
FOR Dt IN ( [08/30/2011],
[08/31/2011],
[09/01/2011],
[09/02/2011],
[09/03/2011])) as P
August 30, 2011 at 9:24 am
I guess I don't understand how I can do that. If the PIVOT sub-query was written as shown below, the whole query fails because a VARCHAR cannot be part of a SUM aggregate.
Where you recommending it be formatted somewhere else which would then forcing the SUM(Amt) result column to be returned in the format as 1,000.00? That's kind of what I'm looking for but don't know where to do it.
SELECT *
FROM (SELECT ID, CONVERT(VARCHAR(100), Amt, 1) AS Amt, CONVERT(VARCHAR(10), Dt, 101) AS Dt
FROM @Budget) AS B
PIVOT ( SUM(Amt)
FOR Dt IN ( [08/30/2011],
[08/31/2011],
[09/01/2011],
[09/02/2011],
[09/03/2011])) as P
August 30, 2011 at 9:26 am
@ColdCoffee, I see where the confusion is and my apologies for not specifying. This query has been simplified from a dynamically built PIVOT query. I actually don't know the number of columns when the query is run. I need to be able to format on the fly, such as show below, but this query doesn't work.
SELECT *
FROM (SELECT ID, Amt, CONVERT(VARCHAR(10), Dt, 101) AS Dt
FROM @Budget) AS B
PIVOT ( CONVERT(VARCHAR(100), SUM(Amt), 1)
FOR Dt IN ( [08/30/2011],
[08/31/2011],
[09/01/2011],
[09/02/2011],
[09/03/2011])) as P
August 30, 2011 at 9:29 am
So don't convert to varchar at all and let your front end handle the formatting.
SELECT *
FROM (SELECT ID, Amt, Dt
FROM @Budget) AS B
PIVOT ( SUM(Amt)
FOR Dt IN ( [08/30/2011],
[08/31/2011],
[09/01/2011],
[09/02/2011],
[09/03/2011])) as P
_______________________________________________________________
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/
August 30, 2011 at 9:29 am
patrick.bewley (8/30/2011)
This query has been simplified from a dynamically built PIVOT query.
I so expected this 🙂 I need to just hash thro some of my old code, i think i have done tat somewhere.. let me see if i can get if for you..
August 30, 2011 at 10:21 pm
And if you must do the formatting in T-SQL, you could replace the pivot by a crosstab. Then you can use max(), which allows for character strings to be handled, instead of sum().
September 7, 2011 at 6:26 pm
R.P.Rozema (8/30/2011)
And if you must do the formatting in T-SQL, you could replace the pivot by a crosstab. Then you can use max(), which allows for character strings to be handled, instead of sum().
Correct... See the last link in R.P.'s signature line abov for how to do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply