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


Aggregating nvarchar(max) column


Aggregating nvarchar(max) column

Author
Message
ravi-441349
ravi-441349
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Attachments
SQL_Server_Central.xlsx (2 views, 14.00 KB)
ChazMan
ChazMan
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 247
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.
ravi-441349
ravi-441349
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
ChazMan
ChazMan
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 247
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.
ravi-441349
ravi-441349
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
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