Help with this query plz?
-
I need to retrieve the latest description on a specific case. I am joining to the following table using the max(index) derived elsewhere in the code by means of a subquery.
The way that the database has designed is that the description for one particular index can span one to n number of records. I need to be able to concatenate all the seperate records into 1 varchar(250) field to include within a report.
Below I have included sample data for one particular record. Thanks for your help
Index LineNo Desc
46913229 1 Test test test124
46913229 2 Test test test241
46913229 3 Test test test012
46913229 4 Test test test741
46913229 5 Test test test665
46913229 6 Test test test423
46913229 7 Test test test478
46913229 8 Test test test4487
46913229 9 Test test test54
-
I'm sure someone with a bigger brain than me could come up with a better solution, but the following does the trick. you could turn the loop into a function that returns the string you want without too much trouble.
Hope it helps.
S
set nocount on
declare @table table
([Index] int, [LineNo] int,[Desc] varchar(20))
declare @LineNo int
declare @MyString varchar(250)
insert into @table
select 46913229, 1,'Test test test124'
union
select 46913229, 2,'Test test test241'
union
select 46913229, 3,'Test test test012'
union
select 46913229, 4,'Test test test741'
union
select 46913229, 5,'Test test test665'
union
select 46913229, 6,'Test test test423'
union
select 46913229, 7,'Test test test478'
union
select 46913229, 8,'Test test test4487'
union
select 46913229, 9,'Test test test54'
select @MyString = ''
select @LineNo = min([LineNo])
from @table
while @LineNo is not null
begin
select @MyString = @MyString + ' ' + [Desc]
from @table
where [LineNo] = @LineNo
select @LineNo = min([LineNo])
from @table
where [LineNo] > @LineNo
end
select @MyString
-
Does this help?
USE SSC
GO
-- How do I concatenate rows into a single column?
-- Short answer is you should never do this when an outside application is presenting the data and that application is able to format the data for you.
-- However if the report is to be run on the server and e-mailed or something like that (no presentation layer), here's how you do it :
-- You first create a function. For the sake of simplicity I will not pass any parameters to the function but the rules that say you should never return more data than needed still applies.
-- Let's say I want to return all system table names into a single column, I would do it like so :
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Concat1' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')
DROP FUNCTION dbo.Concat1
GO
CREATE FUNCTION dbo.Concat1()
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Return AS NVARCHAR(4000)
SELECT @Return = COALESCE (@Return + ', ' + Name, Name) FROM dbo.SysObjects WHERE XType = 'S' ORDER BY Name
RETURN @Return
END
GO
SELECT dbo.Concat1() AS SysTables
/*
SysTables
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
syscolumns, syscomments, sysdepends, sysfilegroups, sysfiles, sysfiles1, sysforeignkeys, sysfulltextcatalogs, sysfulltextnotify, sysindexes, sysindexkeys, sysmembers, sysobjects, syspermissions, sysproperties, sysprotects, sysreferences, systypes, sysusers
(1 row(s) affected)
*/
-- This works great, however we now want to have a list of all the columns for each system table in the same order they are presented in enterprise manager, so the result set we want is now "TableName, ListOfColumns"
-- It would go something like this
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Concat2' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')
DROP FUNCTION dbo.Concat2
GO
CREATE FUNCTION dbo.Concat2(@id AS INT)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @Return AS NVARCHAR(4000)
SELECT @Return = COALESCE (@Return + ', ' + Name, Name) FROM dbo.SysColumns WHERE id = @id ORDER BY Colid
RETURN @Return
END
GO
--Now we need to call the function from within a query instead of simply calling the function all by itself
SELECT Name AS TableName, dbo.Concat2(id) AS ColumnsList FROM dbo.SysObjects WHERE XType = 'S' ORDER BY Name
/*
TableName ColumnsList
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
syscolumns name, id, xtype, typestat, xusertype, length, xprec, xscale, colid, xoffset, bitpos, reserved, colstat, cdefault, domain, number, colorder, autoval, offset, collationid, language, status, type, usertype, printfmt, prec, scale, iscomputed, isoutparam, isnullable, collation, tdscollation
syscomments id, number, colid, status, ctext, texttype, language, encrypted, compressed, text
...
sysusers uid, status, name, sid, roles, createdate, updatedate, altuid, password, gid, environ, hasdbaccess, islogin, isntname, isntgroup, isntuser, issqluser, isaliased, issqlrole, isapprole
(19 row(s) affected)
*/
-
Adam...
What do you want to happen if the amount of data exceeds the VARCHAR(250) column you've outlined?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
same goes for varcharcolumns of 8000
Indeed these kind of constructions will have a cost !
CREATE FUNCTION [dbo].[fn_ALZDBA_GetFullDescription](@Index int)
RETURNS @tblFullDescription TABLE
(RefIndex int not null, Description Text not NULL)
AS
BEGIN
Insert into @tblFullDescription (RefIndex, Description) values (@Index, '')
declare @LineNo int
Set @LineNo = 1
While 1 = 1
begin
Update TVF
set Description = Description + T.Desc
from @tblFullDescription TVF
inner join yourtable T
on TVF.RefIndex = T.Index
and T.LineNo = @LineNo
if @@rowcount = 0 break
Set @LineNo = @LineNo + 1
end
RETURN
END
TEST it TEST it
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi All,
Thanks for your help.
ALZDBA I am trying to test your function now but I cannot understand what values you are passing to the function?
Can you give me an example of how it works based upon by test data as above. If you could use the column names it would help me a lot.
Thanks again
-
I think I have worked out what your doing now. This is what I have so far using the real column names...
I get this error and cannot work out what it relates to, it puts me on this line Update TVF
Server: Msg 403, Level 16, State 1, Procedure fn_GetFullDescription, Line 12
Invalid operator for data type. Operator equals add, type equals text.
CREATE FUNCTION [fn_GetFullDescription](@Index int)
RETURNS @tblFullDescription TABLE
(RefIndex int not null, tddesc Text not NULL)
AS
BEGIN
Insert into @tblFullDescription (RefIndex, tddesc) values (@Index, '')
declare @LineNo int
Set @LineNo = 1
While 1 = 1
begin
Update TVF
set tvf.tddesc = tvf.tddesc + T.tddesc
from @tblFullDescription TVF
inner join TimeDesc T
on TVF.RefIndex = T.tdtcIndex
and T.tdline = @LineNo
if @@rowcount = 0 break
Set @LineNo = @LineNo + 1
end
RETURN
END
-
I changed the text datatype to a varchar(250) and it create the function.
If the data exceeds 250 I want to truncate the data as it will be used in Crystal 8 which doesn't support anything over 250 characters!
-
Ok I have tested the function but it does return an error when the data exceeds 250 characters. What is the best way to get round this?
Use a convert function within the funtion I have created?
-
Declare tddesc as varchar(250) and you should be golden (untested).
-
I've been to quick with my response using a text column Aparently text/image operators cannot be used in functions
I tested this :
CREATE FUNCTION [fn_GetFullDescription](@Index int)
RETURNS @tblFullDescription TABLE
(RefIndex int not null, tddesc Text not NULL)
AS
BEGIN
Insert into @tblFullDescription (RefIndex, tddesc) values (@Index, '')
DECLARE @ptrval varbinary(16)
Declare @BeginPos int
Declare @tddesc varchar(8000)
declare @LineNo int
Set @LineNo = 1
While 1 = 1
begin
SELECT @ptrval = TEXTPTR(TVF.tddesc)
, @tddesc = T.tddesc
, @BeginPos= datalength(TVF.tddesc)
from @tblFullDescription TVF
inner join T_desc T
on TVF.RefIndex = T.[Index]
and T.[LineNo] = @LineNo
and T.[Index] = @index
if @@rowcount = 0 break
UPDATETEXT @tblFullDescription.tddesc @ptrval (@BeginPos + 1) datalength(@tddesc) @tddesc
Set @LineNo = @LineNo + 1
end
RETURN
END
go
select *
, dbo.fn_GetFullDescription(T1.[index])
from T1
And got the error :
Server: Msg 443, Level 16, State 1, Procedure fn_GetFullDescription, Line 39
Invalid use of 'textptr' within a function.
Back to the varchar(8000) solution.
So don't use a TVF but just return the resultstring.
I'm sorry I've put you on a dead end
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
THAT's one of the things I like about you, ALZDBA!! Need more folks that can come back and say "Dang, made a mistake". Rare breed indeed
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Adam,
Are you all set have Remi's suggestion or are you still having problems?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I try to keep my demos as simple as possible, but when you never understood the concept, it still can be pretty hard to understand when you first look at it.
Make sure you get your questions answered, so that maybe I can even further simplify this thing.
-
But it can be used in stored procedure...
Yes, you cannot use SP call in select statement, but if you're bad enough to pass set of more than 8k strings to client then looping would not make it any worse.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply