Newbie Question. Group by on text fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Newbie Question. Group by on text fields

Author

Topic

Rovastar
Starting Member

38 Posts

Posted - 2005-11-15 : 07:00:15

I haven't done SQL since just after uni really so about 7 years ago now.

I was actually good it it then. :)

Anyway all I want to do is this group by a text field.

"
SELECT [myTable].[TextData]
FROM [myTable]
GROUP BY [myTable].[TextData];
"

Where TextData is type ntext.

Can someone explain how I get around this?

MSAccess can do this no problem. But I cannot find out how in SQL server.

I know it will not be efficent but it is simple query only to be run when troubleshooting.

Googling for Group by text is a nightmare and the pages that I think will contain the answer are sign up stuff.........

Can anyone here help?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-15 : 07:11:20

I guess you need to do convert for the same..

Select Convert(varchar(8000),MyTable.TextData) From MyTable
Group By Convert(varchar(8000),MyTable.TextData)

But if in the text columns the data is more then 8000 chars then this may gvie some fiered output..

Complicated things can be done by simple thinking

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-15 : 07:13:41

you can't group on text column
you must convert it to varchar
select max(id), convert(varchar(8000), YourTextColumn) from crmMDLAssignment
group by convert(varchar(8000), YourTextColumn)

this will truncate YourTextColumn at 8000 chars but i think this is sufficient for grouping

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-15 : 07:13:51

That should be

Select Convert(varchar(8000),MyTable.TextData) From MyTable
Group By Convert(varchar(8000),MyTable.TextData)

or

Select Cast(MyTable.TextData as varchar(8000)) From MyTable
Group By Cast(MyTable.TextData as varchar(8000))

Madhivanan

Failing to plan is Planning to fail

Rovastar
Starting Member

38 Posts

Posted - 2005-11-15 : 07:48:53

Thank you folks. That is just what I wanted.

I know it would be easy. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources