Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T SQL Help - Comma separated numric value


T SQL Help - Comma separated numric value

Author
Message
Smash125
Smash125
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 1381
SELECT
YEAR(OrderDate) AS OrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q2
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY 1 ASC

Using above Query i got below report
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 5850932.9483 8476619.278
2002 7379686.3091 8210285.1655 13458206.13 10827327.4904
2003 8550831.8702 10749269.374 18220131.5285 16787382.3141
2004 14170982.5455 17969750.9487 56178.9223 NULL

output should like this

OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 58,50932.9483 84,76619.278
2002 73,79686.3091 82,10285.1655 13,458206.13 10,827327.4904
2003 85,50831.8702 10,749269.374 18,220131.5285 16,787382.3141
2004 14,170982.5455 17,969750.9487 56,178.9223 NULL
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8437
Smash125 (10/12/2012)

SELECT
YEAR(OrderDate) AS OrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q2
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY 1 ASC

Using above Query i got below report
OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 5850932.9483 8476619.278
2002 7379686.3091 8210285.1655 13458206.13 10827327.4904
2003 8550831.8702 10749269.374 18220131.5285 16787382.3141
2004 14170982.5455 17969750.9487 56178.9223 NULL

output should like this

OrderYear Q1 Q2 Q3 Q2
----------- --------------------- --------------------- --------------------- ---------------------
2001 NULL NULL 58,50932.9483 84,76619.278
2002 73,79686.3091 82,10285.1655 13,458206.13 10,827327.4904
2003 85,50831.8702 10,749269.374 18,220131.5285 16,787382.3141
2004 14,170982.5455 17,969750.9487 56,178.9223 NULL


This sort of formatting should be performed in the presentation layer, not the database layer.

If you insist on doing it in the database layer, it can be achieved like this: -
SELECT OrderYear, 
CONVERT(VARCHAR(100), CAST(Q1 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q2 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q3 AS money), 1),
CONVERT(VARCHAR(100), CAST(Q4 AS money), 1)
FROM (SELECT
YEAR(OrderDate) AS OrderYear,
SUM(CASE WHEN DATENAME(Q,OrderDate) =1 THEN TotalDue END)AS Q1,
SUM(CASE WHEN DATENAME(Q,OrderDate) =2 THEN TotalDue END)AS Q2,
SUM(CASE WHEN DATENAME(Q,OrderDate) =3 THEN TotalDue END)AS Q3,
SUM(CASE WHEN DATENAME(Q,OrderDate) =4 THEN TotalDue END)AS Q4
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
) a
ORDER BY OrderYear;




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45131 Visits: 39923
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:


Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8437
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:


Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.


Honestly, I assumed it was a typo in the same way as having two Q2 columns was a typo. If I'm wrong, well, you know what happens when you assume, right? :-D


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Vedran Kesegic
Vedran Kesegic
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1255
Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

ChrisM@home
ChrisM@home
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:


Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.


Lakhs instead of thousands.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
ChrisM@home
ChrisM@home
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
Vedran Kesegic (10/12/2012)
Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.


I can't work out how any of these three functions can help. Please show us.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
ChrisM@home
ChrisM@home
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
Vedran Kesegic (10/12/2012)
Check for STR, CONVERT, and REPLACE functions to additionally format the numbers as string.


I can't work out how any of these three functions can help. Please show us.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10714 Visits: 12017
ChrisM@home (10/14/2012)
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:


Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.


Lakhs instead of thousands.

Maybe, but if so there seems to be also a rule that for amounts less than 1 lakh a comma eparates thousands: this is shown by the last number, where the sated form is 56,178.9223.

Tom

ChrisM@home
ChrisM@home
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
L' Eomot Inversé (10/14/2012)
ChrisM@home (10/14/2012)
Jeff Moden (10/12/2012)
Cadavre (10/12/2012)
If you insist on doing it in the database layer, it can be achieved like this:


Check again. OP's desired output doesn't actually have a comma as a thousands separator. It would appear that the OP wants a comma only after the 2nd character of the integer part of each number.


Lakhs instead of thousands.

Maybe, but if so there seems to be also a rule that for amounts less than 1 lakh a comma eparates thousands: this is shown by the last number, where the sated form is 56,178.9223.


Fair point, Tom, but if you examine all of those "output example" figures and count the number of digits between the comma and the decimal point, this is what you get:

56,178.9223     -- 3

58,50932.9483 -- 5
84,76619.278 -- 5
73,79686.3091 -- 5
82,10285.1655 -- 5
85,50831.8702 -- 5

10,749269.374 -- 6
18,220131.5285 -- 6
16,787382.3141 -- 6
14,170982.5455 -- 6
17,969750.9487 -- 6
13,458206.13 -- 6
10,827327.4904 -- 6


I reckon the intent is lakh, and the rest is down to sloppiness. But it's a guess.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search