Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T SQL Help - Comma separated numric value Expand / Collapse
Author
Message
Posted Friday, October 12, 2012 7:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:24 AM
Points: 194, Visits: 1,087

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
Post #1372100
Posted Friday, October 12, 2012 7:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 2,422, Visits: 7,437
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;




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1372130
Posted Friday, October 12, 2012 8:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:45 PM
Points: 36,747, Visits: 31,194
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1372158
Posted Friday, October 12, 2012 8:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 2,422, Visits: 7,437
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?



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1372162
Posted Friday, October 12, 2012 2:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
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
Post #1372354
Posted Sunday, October 14, 2012 7:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 1,074, Visits: 6,356
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
Post #1372499
Posted Sunday, October 14, 2012 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 1,074, Visits: 6,356
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
Post #1372500
Posted Sunday, October 14, 2012 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 1,074, Visits: 6,356
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
Post #1372501
Posted Sunday, October 14, 2012 7:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 8,556, Visits: 9,047
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
Post #1372504
Posted Sunday, October 14, 2012 9:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 1,074, Visits: 6,356
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
Post #1372512
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse