August 24, 2013 at 8:38 am
i have this query
SELECT ContactID,
AVG(TotalDue) AS [Avergare Total Due]
FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY 1 ASC
what i want to i want prefix dollar symbol in the [Avergare Total Due]
ContactId [Avergare Total Due]
1$25109.7174
2$38125.461
3$57475.5877
4$5125
5$75337.5394
August 24, 2013 at 9:37 am
Read the following thread. It should provide some examples and a caution about doing the concatenation at the database level rather than the presentation level
http://www.sqlservercentral.com/Forums/Topic1407950-392-1.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2013 at 1:26 pm
Smash125 (8/24/2013)
i have this querySELECT ContactID,
AVG(TotalDue) AS [Avergare Total Due]
FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY 1 ASC
what i want to i want prefix dollar symbol in the [Avergare Total Due]
ContactId [Avergare Total Due]
1$25109.7174
2$38125.461
3$57475.5877
4$5125
5$75337.5394
This looks a lot like homework but even if it's not, let's get you used to looking in Books Online for help. The process you're looking for is called "concatenation". Lookup "concatenation" in the index of Books Online. You should also lookup "cast" and "convert" which will not only allow you to do the necessary conversion you'll need to do to the number, but it'll allow you to make the additional formatting mistake of adding commas in all the right places.
If you don't know how to get to Books OnLine (affectionately known as "BOL"), open SSMS and press the {f1} key.
As the others have stated, formatting data using T_SQL should generally be left up to the front end, if there's a front end.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2013 at 2:44 am
SELECT ContactID,Concat('$',AVG(TotalDue)) AS [Avergare Total Due]
FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY 1 ASC
August 26, 2013 at 2:54 am
kakumanukalyan (8/26/2013)
SELECT ContactID,Concat('$',AVG(TotalDue)) AS [Avergare Total Due]FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY 1 ASC
Not really a fan of doing this in the database layer. If the result is NULL, you get a single $ with the concat function. This seems odd in reports. As others have already pointed out numerous times, keep it in the presentation layer.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 9:06 am
Koen Verbeeck (8/26/2013)
kakumanukalyan (8/26/2013)
SELECT ContactID,Concat('$',AVG(TotalDue)) AS [Avergare Total Due]FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY 1 ASC
Not really a fan of doing this in the database layer. If the result is NULL, you get a single $ with the concat function. This seems odd in reports. As others have already pointed out numerous times, keep it in the presentation layer.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 27, 2013 at 8:21 am
Koen Verbeeck (8/26/2013)
kakumanukalyan (8/26/2013)
SELECT ContactID,Concat('$',AVG(TotalDue)) AS [Avergare Total Due]FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY 1 ASC
Not really a fan of doing this in the database layer. If the result is NULL, you get a single $ with the concat function. This seems odd in reports. As others have already pointed out numerous times, keep it in the presentation layer.
If you do:
'$' + AVG(TotalDue) AS [Avergare Total Due]
Doesn't SQL just throw a NULL instead of a lone '$'? I thought that was the default behavior for concatenating NULL values. Or you could throw an ISNULL on there to show $0 instead.
August 27, 2013 at 9:31 am
erikd (8/27/2013)
Koen Verbeeck (8/26/2013)
kakumanukalyan (8/26/2013)
SELECT ContactID,Concat('$',AVG(TotalDue)) AS [Avergare Total Due]FROM Sales.SalesOrderHeader
GROUP BY ContactID
ORDER BY 1 ASC
Not really a fan of doing this in the database layer. If the result is NULL, you get a single $ with the concat function. This seems odd in reports. As others have already pointed out numerous times, keep it in the presentation layer.
If you do:
'$' + AVG(TotalDue) AS [Avergare Total Due]
Doesn't SQL just throw a NULL instead of a lone '$'? I thought that was the default behavior for concatenating NULL values. Or you could throw an ISNULL on there to show $0 instead.
Here are some examples to demonstrate. The first set is SQL 2008 and demonstrates some of the issues with just concatenating the results as you did in your code. Pay attention to snippets 1,2, and 4.
/* Notice how the $ does not actually concatenate */
DECLARE @moneyconcat TABLE (ContactId INT PRIMARY KEY Clustered,AverageTotalDue MONEY)
INSERT INTO @moneyconcat
( ContactId,AverageTotalDue )
VALUES ( 1,25109.7174
),(2,38125.461)
,(3,57475.5877)
,(4,5125)
,(5,75337.5394)
SELECT '$' + AVG(AverageTotalDue) AS [Average Total Due]
FROM @moneyconcat
/* Notice how the $ does not actually concatenate with these negative values */
DECLARE @moneyconcat2 TABLE (ContactId INT PRIMARY KEY Clustered,AverageTotalDue MONEY)
INSERT INTO @moneyconcat2
( ContactId,AverageTotalDue )
VALUES ( 1,25109.7174
),(2,38125.461)
,(3,-57475.5877)
,(4,5125)
,(5,-75337.5394)
SELECT '$' + AVG(AverageTotalDue) AS [Average Total Due]
FROM @moneyconcat2
/* convert to varchar so $ can be concated
works fine with a positive value */
DECLARE @moneyconcat3 TABLE (ContactId INT PRIMARY KEY Clustered,AverageTotalDue MONEY)
INSERT INTO @moneyconcat3
( ContactId,AverageTotalDue )
VALUES ( 1,25109.7174
),(2,38125.461)
,(3,57475.5877)
,(4,5125)
,(5,75337.5394)
SELECT '$' + CONVERT(VARCHAR(20),AVG(AverageTotalDue)) AS [Average Total Due]
FROM @moneyconcat3
/* convert to varchar so $ can be concated
observe the behavior of the negative value */
DECLARE @moneyconcat4 TABLE (ContactId INT PRIMARY KEY Clustered,AverageTotalDue MONEY)
INSERT INTO @moneyconcat4
( ContactId,AverageTotalDue )
VALUES ( 1,25109.7174
),(2,38125.461)
,(3,-57475.5877)
,(4,5125)
,(5,-75337.5394)
SELECT '$' + CONVERT(VARCHAR(20),AVG(AverageTotalDue)) AS [Average Total Due]
FROM @moneyconcat4
Now onto the SQL 2012 code example where concatenating is a little easier. Take note of the results in the second snippet
DECLARE @moneyconcat TABLE (ContactId INT PRIMARY KEY Clustered,AverageTotalDue MONEY)
INSERT INTO @moneyconcat
( ContactId,AverageTotalDue )
VALUES ( 1,25109.7174
),(2,38125.461)
,(3,57475.5877)
,(4,5125)
,(5,75337.5394)
SELECT ContactID,Concat('$',AVG(AverageTotalDue)) AS [Average Total Due]
FROM @moneyconcat
GROUP BY ContactID
ORDER BY 1 ASC
/* This will produce a record with a Null Value */
DECLARE @moneyconcat2 TABLE (ContactId INT PRIMARY KEY Clustered,AverageTotalDue MONEY)
INSERT INTO @moneyconcat2
( ContactId,AverageTotalDue )
VALUES ( 1,25109.7174
),(2,38125.461)
,(3,57475.5877)
,(4,NULL)
,(5,75337.5394)
SELECT ContactID,Concat('$',AVG(AverageTotalDue)) AS [Average Total Due]
FROM @moneyconcat2
GROUP BY ContactID
ORDER BY 1 ASC
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 27, 2013 at 9:40 am
Ah. That's weird. Why does that happen? I jumped from 2005 to 2012, and never ran into that happening. I feel pretty lucky :Whistling:
August 27, 2013 at 10:42 am
erikd (8/27/2013)
Ah. That's weird. Why does that happen? I jumped from 2005 to 2012, and never ran into that happening. I feel pretty lucky :Whistling:
I should probably clarify a little more. The first set of scripts will produce the same results on 2008, 2008 R2, and 2012. The concat is a new command in 2012 and is the only reason I was distinguishing between the two sets.
I haven't tested the first set on 2005. But in the end, this is why we recommend that formatting be done in the presentation layer.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 27, 2013 at 10:47 am
SQLRNNR (8/27/2013)
erikd (8/27/2013)
Ah. That's weird. Why does that happen? I jumped from 2005 to 2012, and never ran into that happening. I feel pretty lucky :Whistling:I should probably clarify a little more. The first set of scripts will produce the same results on 2008, 2008 R2, and 2012. The concat is a new command in 2012 and is the only reason I was distinguishing between the two sets.
I haven't tested the first set on 2005. But in the end, this is why we recommend that formatting be done in the presentation layer.
Understood. In my case, SQL queries are more often than not the presentation layer for a lot of data. So I end up doing things that would make a lot of you cringe.
Anyone want a scalar valued UDF to put names in proper case? :blush:
August 27, 2013 at 10:49 am
erikd (8/27/2013)
SQLRNNR (8/27/2013)
erikd (8/27/2013)
Ah. That's weird. Why does that happen? I jumped from 2005 to 2012, and never ran into that happening. I feel pretty lucky :Whistling:I should probably clarify a little more. The first set of scripts will produce the same results on 2008, 2008 R2, and 2012. The concat is a new command in 2012 and is the only reason I was distinguishing between the two sets.
I haven't tested the first set on 2005. But in the end, this is why we recommend that formatting be done in the presentation layer.
Understood. In my case, SQL queries are more often than not the presentation layer for a lot of data. So I end up doing things that would make a lot of you cringe.
Anyone want a scalar valued UDF to put names in proper case? :blush:
:-D:hehe:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply