July 7, 2008 at 2:06 pm
hi guys is there any way to create a function to put a number of values into different rows. For example:
Imagine you have multiple columns like this:
One|Two|Three|Four|Five
The output you desire is to combine all the columns and put it as different rows similar to the following:
Numbers
---------
One
Two
Three
Four
Five
is this possible? my issue is that from my application side i have an input parameter @Numbers which inputs the data in that format so i would like to be able to use that input parameter like this:
where RP_Financials.NumberFN_FK NOT IN ((SELECT Numbers from newfunction('One'|'Two'|'Three'|'Four'|'Five'))
July 7, 2008 at 2:27 pm
Sure, create a Tally table and use the below function ....
(compliments to Jeff Moden)
ALTER FUNCTION [dbo].[split]
(@String VARCHAR(8000)
,@Delimiter VARCHAR(5))
RETURNS @RtnValue TABLE
(arrIndex INT IDENTITY(1,1)
,arrText VARCHAR(8000))
AS BEGIN
-- we pad the string with delimiters if they aren't already there
SELECT
@String = CASE WHEN LEFT(RTRIM(LTRIM(@String)),1) <> @Delimiter
THEN @Delimiter + RTRIM(LTRIM(@String))
+ CASE WHEN RIGHT(RTRIM(LTRIM(@String)),1) <> @Delimiter
THEN @Delimiter
ELSE ''
END
END
INSERT INTO @RtnValue (arrText)
SELECT
SUBSTRING(@String,N + 1,CHARINDEX(@Delimiter,@String,N + 1) - N - 1)
FROM
dbo.Tally
WHERE
N < LEN(@String) --Don't include the last delimiter
AND SUBSTRING(@String,N,1) = @Delimiter --Find the delimiters
RETURN
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 7, 2008 at 2:34 pm
wow! thanks i will go ahead and test it thank you so much.
July 7, 2008 at 3:47 pm
You can also use SQL 2005's PIVOT function.
Doug
July 8, 2008 at 7:46 am
can you please explain to me a little bit more about the pivot function and how can i use it in this case?
July 8, 2008 at 3:02 pm
XML alternative:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fListToVarchars]( @list varchar(max), @delim varchar(16) )
returns @returnTable table
( item varchar(255) not null, itemSequence smallint not null )
as begin
declare @xml XML
set @xml = char(60)+'item>' + REPLACE(REPLACE(@list,char(60),' '
insert into @returnTable
SELECT ltrim(data.item.value('.','varchar(255)')), row_number() over (order by getdate())
FROM @xml.nodes('//item') as data(item)
return
end
;
select * from dbo.fListToVarchars('One|Two|Three|Four|Five','|')
July 8, 2008 at 5:50 pm
thank you!, both functions would do the same as using unpivot?
July 18, 2008 at 10:56 am
what parameters should i use for the tally table? can i do it without creating one?
July 18, 2008 at 2:12 pm
DBA (7/18/2008)
what parameters should i use for the tally table? can i do it without creating one?
You can avoid creating one, but I recommend creating one. Highly recommend it. (I call mine a Numbers table, not Tally, but it does the same thing.)
create table dbo.Numbers (
Number int primary key)
go
insert into dbo.Numbers(Number)
select top 10001 row_number() over (order by s1.object_id)-1
from sys.all_objects s1
cross join sys.all_objects s2
order by s1.object_id
(You can use more or less copies of sys.all_objects, depending on how big a table you need. I keep mine with all numbers between 0 and 10,000 in it.)
Since I use this in multiple databases on each server, I have a database called "Common" where I keep the Numbers table (and Calendar table, and a few other tables and functions with pretty much static data in them). Then create a synonym in each database, and you're good to go.
They have so many uses, you'll definitely want to create one. If you really can't, then use can use a CTE in the place of one, but it is a bit slower.
;with Number (Number) as
(select top 10001 row_number() over (order by s1.object_id)-1
from sys.all_objects s1
cross join sys.all_objects s2
order by s1.object_id)
But you have to put that at the top of each query that will use the Numbers table. (Use Tally if you like. The name doesn't matter.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 8:45 am
thank you, what do you mean with a CTE, in my case i will use this function for a variable (int) , i don' think its necessary to create a table when my variable will only be a couple of numbers example 52|51|55|63
July 29, 2008 at 8:59 am
Thank you so much for your xml function, i am getting an error The replace function requires 3 argument(s)
July 29, 2008 at 12:05 pm
sorry, but xml always seems to get mangled in the post body. here's the function as an attachment.
July 29, 2008 at 12:31 pm
it worked perfectly thank you so much!
July 29, 2008 at 3:06 pm
DBA (7/29/2008)
thank you, what do you mean with a CTE, in my case i will use this function for a variable (int) , i don' think its necessary to create a table when my variable will only be a couple of numbers example 52|51|55|63
The CTE can be used in the place of a Numbers/Tally table. It's a "second best" option, since a Numbers table is so much better.
The point of the table isn't to store the numbers in your string, it's to help querying it. Parsing a delimited string with a Numbers/Tally table is the fastest way to do it. The number of items in the string doesn't matter so much, it's the method that counts.
Antonio's XML method is pretty good. A Numbers table is slightly faster, and can deal with things that the XML method can't (like strings with carets or ampersands in them), which is why I recommend it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 7:01 pm
actually fListToVarchars can handle xml markup with a judicious use of replace(). just replace char(60) with '<' and char(38) with '&'. i posted an old version without that enhancement. here's the update.
select * from global.dbo.fListToVarchars('me&you is < us',' ')
[font="Courier New"]item itemSequence
me&you 1
is 2
< 3
us 4[/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply