September 11, 2008 at 2:34 pm
I have a request to create a column that stores all the comments for an account into one column. There can be an unlimited number of comments...
Acct Comment
1 "Comment 1"
1 "Comment 2"
1 "Comment 3"
1 "Comment 4"
etc...
Needs to be
Acct Comment
1 "Comment 1, Comment 2, Comment 3, Comment 4, etc..."
Any thoughts?
September 11, 2008 at 3:08 pm
Yes, screw them. Select the data as multiple rows. Present into a single text object... or use a concatenation on the server to present the data (straight in the select).
September 11, 2008 at 3:09 pm
Try this "how to" on for size....
http://qa.sqlservercentral.com/articles/Test+Data/61572/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 11, 2008 at 3:10 pm
Hello,
Concatenating strings is no problem but you may have to get a bit creative to avoid the limitations on text lenghts.
Look at the spec difference between varchar's and text.
We would need to know a bit more about this 'unlimited comments' goal. That usually means that there are only a few comments and not several thousand charcters or a book. Without knowing the limits of this 'unlimited'-ness it's hard to suggest solutions.
One other design besides just concenating comments into one field is to create a table with each row a specified length ( say varchar(80) ) and then populating the text into each record with an embedded delimiter that your decriptor logic can detect to roll the comments back out again in human readable form.
Hope this helps.
Regards,
Terry
September 11, 2008 at 3:15 pm
The Comment field in the existing table is VarChar(2000).
September 11, 2008 at 3:18 pm
What do you mean by concatenate on the server???
September 11, 2008 at 3:36 pm
Can this be accomplished in any sort of cursor?
September 11, 2008 at 3:46 pm
September 11, 2008 at 3:47 pm
Have you read the link that Matt posted? After reading the whole article, take a look at the code section just above the conclusion. That code should be able to give you the result you are looking for.
I believe the point Ninja was making, is that there is no need to "store" the data in a single column. Instead, use something like the code noted above that returns the multiple records in a single result. Based on the information we have so far (which is admittedly limited), storing that data in one column would not be a good idea, for quite a few reasons.
As is usually the case, we don't have much information here to know your whole situation, but it sure sounds like a case of a front end developer not being able to put the multiple rows together on the app side, which is the better place to do it (usually). Considering that you have a column that is varchar(2000), is separating them by a comma really going to do the trick?
GL!
September 11, 2008 at 3:52 pm
Good points... another questions to check with that varchar(2000) is how much of that is actually used. I've seen description fields as varchar(8000) with never more than 25 characters used...
What was the intention behind the design VS what is the actual need in the application.
I personnally preffer to have multiple comment rows than a single convulted text field... you can keep more data like commentdates, by who, in reference of...
September 19, 2008 at 10:12 am
I read the article referenced above and I'm attempting to apply the concatenation code to my own situation, but I'm failing miserably. What I'm trying to do is return a single field with the days of the week for a course. For instance, CHE 1040 is offered Mon, Wed, Fri and then a longer session on Thursday in a lab. (Hence the two separate records). So, what I was expecting was to be able to do
select dbo.fnconcattest('che 104001') and have it return 'MWF,R'. It's not doing that. It's giving me an error about having two rows. So, it's not concatenating. Below is the code to create the table with the two records and then my miserable attempt at the code for the function.
CREATE TABLE [dbo].[SECTIONSCHEDULE](
[ACADEMIC_YEAR] [varchar](4) NOT NULL,
[ACADEMIC_TERM] [varchar](10) NOT NULL,
[ACADEMIC_SESSION] [varchar](10) NOT NULL,
[EVENT_ID] [varchar](15) NOT NULL,
[EVENT_SUB_TYPE] [varchar](4) NOT NULL,
[SECTION] [varchar](4) NOT NULL,
[DAY] [varchar](4) NOT NULL,
[START_TIME] [datetime] NOT NULL,
[END_TIME] [datetime] NOT NULL,
[ORG_CODE_ID] [varchar](10) NULL,
[BUILDING_CODE] [varchar](6) NULL,
[ROOM_ID] [varchar](6) NULL,
[CREATE_DATE] [datetime] NOT NULL,
[CREATE_TIME] [datetime] NOT NULL,
[CREATE_OPID] [varchar](8) NOT NULL,
[CREATE_TERMINAL] [varchar](4) NOT NULL,
[REVISION_DATE] [datetime] NOT NULL,
[REVISION_TIME] [datetime] NOT NULL,
[REVISION_OPID] [varchar](8) NOT NULL,
[REVISION_TERMINAL] [varchar](4) NOT NULL,
[ABT_JOIN] [varchar](1) NOT NULL,
[CALENDARDET_EVENT_KEY] [int] NOT NULL,
[SECTIONSCHEDULE_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pkSectionSchedule] PRIMARY KEY CLUSTERED
(
[SECTIONSCHEDULE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
insert sectionschedule
(ACADEMIC_YEAR, ACADEMIC_TERM, ACADEMIC_SESSION,EVENT_ID,EVENT_SUB_TYPE,SECTION,DAY,START_TIME,END_TIME,ORG_CODE_ID,BUILDING_CODE,ROOM_ID,CREATE_DATE,CREATE_TIME,CREATE_OPID,CREATE_TERMINAL,REVISION_DATE,REVISION_TIME,REVISION_OPID,REVISION_TERMINAL,ABT_JOIN,CALENDARDET_EVENT_KEY)
values ('2008','FALL','TRAD','CHE 1040','LEC','01','MWF','1900-01-01 11:00:00.000','1900-01-01 11:50:00.000','O180900000','GREAT','410','2008-01-14 00:00:00.000','1900-01-01 14:41:24.000','LRAUM','0001','2008-02-07 00:00:00.000','1900-01-01 09:27:50.170','LRAUM','0001','*','13138')
insert sectionschedule
(ACADEMIC_YEAR, ACADEMIC_TERM, ACADEMIC_SESSION,EVENT_ID,EVENT_SUB_TYPE,SECTION,DAY,START_TIME,END_TIME,ORG_CODE_ID,BUILDING_CODE,ROOM_ID,CREATE_DATE,CREATE_TIME,CREATE_OPID,CREATE_TERMINAL,REVISION_DATE,REVISION_TIME,REVISION_OPID,REVISION_TERMINAL,ABT_JOIN,CALENDARDET_EVENT_KEY)
values ('2008','FALL','TRAD','CHE 1040','LEC','01','R','1900-01-01 10:45:00.000','1900-01-01 13:25:00.000','O180900000','GREAT','215','2008-01-14 00:00:00.000','1900-01-01 14:41:24.000','LRAUM','0001','2008-02-07 00:00:00.000','1900-01-01 09:27:50.170','LRAUM','0001','*','13138')
--function
--note: I plan to pass the eventID plus the section number as @eventID
CREATE FUNCTION dbo.fnConcatTest (@eventID varchar(12))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @Return VARCHAR(255)
SELECT @Return = ISNULL(@Return+',','')+
(select [day] from sectionschedule
where event_Id+section=@eventID
and academic_year='2008'
and academic_term='fall')
--ORDER BY RowNum
RETURN @Return END
When I try to run select dbo.fnconcattest('che 104001'), I get
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
September 19, 2008 at 2:15 pm
I figured it out on my own! In fact, I used the article Matt posted and another article, too, and found two ways. Here's what I used.
--option 1
create function [dbo].[TNU_fnGetClassroomDOW]
(
@event_ID varchar(12)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @colList varchar(255),
@CurrentYear varchar(4),
@CurrentTerm varchar(12)
set @CurrentYear=(select setting from abt_settings where label_name='current_year')
set @CurrentTerm=(select setting from abt_settings where label_name='current_term')
SELECT @colList = COALESCE(@colList + ' ', '') + [day]
FROM sectionschedule
WHERE event_ID+section=@event_ID
and academic_year=@CurrentYear
and academic_term=@CurrentTerm
RETURN @colList
END
--option two
CREATE FUNCTION dbo.fnConcatTest (@eventID varchar(12))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @return VARCHAR(255),
@CurrentYear varchar(4),
@CurrentTerm varchar(12)
set @CurrentYear=(select setting from abt_settings where label_name='current_year')
set @CurrentTerm=(select setting from abt_settings where label_name='current_term')
SELECT @Return = ISNULL(@Return+' ','')+ [day]+' '+convert(varchar,datetime,
from sectionschedule
where event_Id+section=@eventID
and academic_year=@CurrentYear
and academic_term=@CurrentTerm
RETURN @Return END
September 19, 2008 at 5:04 pm
tbeadle (9/11/2008)
Hello,Concatenating strings is no problem but you may have to get a bit creative to avoid the limitations on text lenghts.
Look at the spec difference between varchar's and text.
We would need to know a bit more about this 'unlimited comments' goal. That usually means that there are only a few comments and not several thousand charcters or a book. Without knowing the limits of this 'unlimited'-ness it's hard to suggest solutions.
One other design besides just concenating comments into one field is to create a table with each row a specified length ( say varchar(80) ) and then populating the text into each record with an embedded delimiter that your decriptor logic can detect to roll the comments back out again in human readable form.
Hope this helps.
Regards,
Terry
All that would be true if this were an SQL Server 2000 forum... it's not... VARCHAR(MAX) will take care of just about any problem in SQL Server 2005...;)
--Jeff Moden
September 19, 2008 at 5:08 pm
lduvall (9/19/2008)
I figured it out on my own! In fact, I used the article Matt posted...
That's good feedback... thanks. I always wonder if articles like that help folks. 😀
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply