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

Aggregating nvarchar(max) column Expand / Collapse
Author
Message
Posted Tuesday, March 1, 2011 12:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 4:20 AM
Points: 3, Visits: 54
Hi,

I have a particular requirement where there is a particular column in a table that stores all the data types. This column is defined as nvarchar(max). This column holds values for datatypes BOOLEAN, DATE,TEXT, CURRENCY and NUMBER. There are other columns in this table on which I need to do group by. Based on the other columns we will only get one data type i.e CURRENCY or NUMBER or DATE or TEXT.

The sample data is attached. I want to pick maximum of currency, numbers and date based on what is the highest. for text max on character sorting.

Initially i tried

select datatype,max(value) from getmaxvalues group by datatype

but the above query gives the character max even for currency, numbers. For date it somehow works fine.

then i tried the below query. it gives conversion error

SELECT
DataType,MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE
Value
END) AS MaxVal
FROM GetMaxValues
GROUP BY DataType

I modified it as below. but still gives conversion error

select
DataType,MAX(
CASE WHEN ISNUMERIC(VALUE)=1 THEN CAST(Value as DECIMAL(18,2))
WHEN ISDATE(VALUE)=1 THEN CAST(Value as DATE)
ELSE VALUE END)
FROM
GetMaxValues
group by datatype

I know that what I am trying to do is not the correct way but not sure on how to get this done.

Any help/pointers would be greatly appreciated

Thanks,
Ravi


  Post Attachments 
SQL_Server_Central.xlsx (1 view, 14.50 KB)
Post #1071515
Posted Tuesday, March 1, 2011 1:04 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:15 AM
Points: 177, Visits: 246
First off...yes this is the WRONG way to do things.

You won't be able to do it in a single field, but you can do it with multiple fields (i think):

Using your code sample, try the following for each type of field you want:

SELECT 
DataType,
MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE NULL END) AS MaxNumber,
MAX(
CASE WHEN DataType in ('Date') THEN CAST(Value AS Datetime)
ELSE NULL END) AS MaxDate,
FROM GetMaxValues
GROUP BY DataType

Hope this works!


--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Post #1071533
Posted Tuesday, March 1, 2011 1:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 4:20 AM
Points: 3, Visits: 54
Thanks for the quick reply.

That works. but the problem is I am getting the multiple columns. I think I need to figure out a way to merge these multiple columns in to one.

Appreciate your help anyway.

Thanks,
Ravi
Post #1071541
Posted Tuesday, March 1, 2011 1:26 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:15 AM
Points: 177, Visits: 246
ok then...do this... (use the query i sent you as a subquery...then collapse the columns) -- typing as i think

SELECT DataType, 
MAX(CASE
WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(MaxNumer as varchar)
WHEN DataType in ('Date') THEN CAST(MaxDate as varchar)
ELSE NULL END) as MaxFieldValue
FROM (
SELECT
DataType,
MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE NULL END) AS MaxNumber,
MAX(
CASE WHEN DataType in ('Date') THEN CAST(Value AS Datetime)
ELSE NULL END) AS MaxDate,
FROM GetMaxValues
GROUP BY DataType
) T1
GROUP BY DataType



--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Post #1071551
Posted Tuesday, March 1, 2011 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 4:20 AM
Points: 3, Visits: 54
Thanks once again. I was figuring out that and your post helped me.

I have tweaked it a bit as max is working fine for date's stored in nvarchar column

here is the final one.


SELECT DATATYPE,ISNULL(MAXNUMBER,'')+ISNULL(MAXVALUE,'')
FROM
(
SELECT
DataType,
CAST(
MAX(
CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2))
ELSE NULL END)
AS NVARCHAR(MAX)
)
AS MaxNumber,
MAX(CASE WHEN DataType NOT in ('CURRENCY','NUMBER') THEN VALUE ELSE NULL END) as MAXVALUE
FROM GetMaxValues
GROUP BY DataType
) A



Thanks,
Ravi
Post #1071564
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse