December 18, 2008 at 9:51 am
Hi,
Thank you so much for the query and it is returning the result that I want.
What should I do, if the string to split has dynamic number of words instead of 3?
[dbo].[colsplit](treatment_values,0) ,
[dbo].[colsplit](treatment_values,1) ,[dbo].[colsplit](treatment_values,2)
How to get the column names in this case?
Thanks,
Maria
December 18, 2008 at 10:35 am
you definetely need a stored proc.
December 18, 2008 at 11:17 am
Can you tell me how to do that?
I am sorry I am new to this.
Thanks for your help,
-Maria
December 18, 2008 at 11:32 am
there might be some glitches. please test thoroughly.
CREATE PROCEDURE splitColsAsSP
AS
BEGIN
SET NOCOUNT ON;
DECLARE @col1 nvarchar(50),
@col2 nvarchar(50),
@col3 nvarchar(500),
@tcol nvarchar(50),
@tcolname nvarchar(50),
@sqlstr nvarchar(500),
@tsqlstr nvarchar(250),
@which int,
@lpos int,
@rpos int,
@firstrow int
declare yourCursor CURSOR FOR
select col1,col2,col3 from yourtable
CREATE TABLE #TempTable
(
col1 nvarchar(50),
col2 nvarchar(50)
)
set @firstrow = 0
open yourCursor
FETCH NEXT FROM yourCursor INTO @col1,@col2,@col3
WHILE @@FETCH_STATUS = 0
BEGIN
set @col3 = @col3 + ','
set @lpos = 0
set @which = 1
set @sqlstr = ''
comma_loop:
set @rpos = charindex(',',@col3,@lpos+1)
if @rpos > 0
begin
set @tcol = substring(@col3,@lpos+1,(@rpos-@lpos-1))
set @tcolname = 'col' + cast(@which+2 as nvarchar(50))
if (@firstrow = 0)
begin
set @tsqlstr = 'ALTER TABLE #TempTable add ' + @tcolname + ' NVARCHAR(50)'
exec sp_executesql @tsqlstr
end
if len(@sqlstr) > 0
begin
set @sqlstr = @sqlstr + ','
end
set @sqlstr = @sqlstr + '''' + @tcol + ''''
set @lpos = @rpos
set @which = @which + 1
goto comma_loop
end
set @firstrow = 1
set @sqlstr = 'insert into #TempTable values(''' + @col1 + ''',''' + @col2 + ''',' + @sqlstr + ')'
exec sp_executesql @sqlstr
FETCH NEXT FROM yourCursor INTO @col1,@col2,@col3
END
select * from #TempTable
close yourCursor
deallocate yourCursor
END
GO
December 18, 2008 at 7:24 pm
psmg01 (12/18/2008)
Hi Jeff,I tried your tally table and splitting the string as rows in a table output.
But how to make them as columns? or use crosstab to make it as a column?
For eg.
"test,to split" returns as 3 rows
test
to
split
But I want it as
test to split - as a 3 column data along with other entries
Thanks,
Maria
Correct... use a CrossTab to turn things into columns. If the number of columns is dynamic, you may want to give the following article a try...
http://qa.sqlservercentral.com/articles/cross+tab/65048/
The section you would probably want is "Converting the Cross Tab to Dynamic SQL"
--Jeff Moden
December 19, 2008 at 1:47 am
psmg01 (12/18/2008)
Can you tell me how to do that?I am sorry I am new to this.
Thanks for your help,
-Maria
RAC is at your service for splitting delimited strings. For example:
create table Maria(col1 int primary key,col2 int,col3 varchar(50))
insert Maria values(1,2,'Hello,Maria')
insert Maria values(2,5,'Rac,splits,strings,into,columns,or,rows.')
insert Maria values(3,6,'The,strings,are,split,dynamically.')
insert Maria values(4,1,'The,string,can,be,split,by,position,or,value.')
insert Maria values(5,7,'Its,easy,and,powerful,and,saves,you,time.')
insert Maria values(8,3,'Rac,is,for,users,inexperienced,in,complicated,sql.')
insert Maria values(9,4,'You,can,lead,a,horse,to,water.')
insert Maria values(10,2,'But,that,doesnt,mean,you,can,make,him,drink:)')
Exec Rac
@split='[position]',
@separator=',',
@rows='col1 & col2',
@pvtcol='col3',
@from='Maria',
@rank='word',@defaults1='y',@pformat='pvtcols',@shell='n'
col1 col2 word1 word2 word3 word4 word5 word6 word7 word8 word9
---- ---- ----- ------- ------- -------- ------------- ----- ----------- ----- -------
1 2 Hello Maria
2 5 Rac splits strings into columns or rows.
3 6 The strings are split dynamically.
4 1 The string can be split by position or value.
5 7 Its easy and powerful and saves you time.
8 3 Rac is for users inexperienced in complicated sql.
9 4 You can lead a horse to water.
10 2 But that doesnt mean you can make him drink:)
Feed your crosstabs and strings to RAC @
Feed your head @
December 19, 2008 at 8:07 am
Hi,
RAC seems to be a cool tool.
Is it free for universities/research projects?
Thanks,
Maria
December 19, 2008 at 4:06 pm
Heh... Just what you were hoping for, huh, Steve?
--Jeff Moden
December 19, 2008 at 6:40 pm
psmg01 (12/19/2008)
Hi,RAC seems to be a cool tool.
Is it free for universities/research projects?
Thanks,
Maria
Hello,
Well it's priced far less than many textbooks universities use:) If $ is really a problem I'll work it out with you. What university are you working at? What kind of research project is it? Bear in mind that while RAC spits out crosstabs and pivots data in different ways that was not the major goal in developing it. The primary goal was to develop a general problem solving tool that was relatively easy to use and would obviate the need of non-expert sql users to write/use/maintain complex sql code. It was intended to visualize solutions in different ways without the sql code itself getting in the way. You'd be surprised how many thick headed university level faculty cannot grasp this simple idea:) RAC is sweet expediency, nothing more, nothing less:)
best,
steve
December 19, 2008 at 6:52 pm
Jeff Moden (12/19/2008)
Heh... Just what you were hoping for, huh, Steve?
That strikes me as an interesting question. I'm not sure if it's the case that right now I don't have an articulate answer or I'm too embarrassed to write it. Gonna have to get back to you on that one ;):)
December 19, 2008 at 11:40 pm
---------------------------------
-- Author: liangCK ??
-- Date : 2008-11-17 17:00:09
---------------------------------
--> ??????: [T]
IF OBJECT_ID('[T]') IS NOT NULL DROP TABLE [T]
CREATE TABLE [T] (col VARCHAR(50))
INSERT INTO [T]
SELECT 'aa,bb,cc' UNION ALL
SELECT 'AAA,BB' UNION ALL
SELECT 'AAA'
--SQL????:
GO
CREATE FUNCTION dbo.array(@ArrayString VARCHAR(MAX),@Delimiter CHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @t TABLE
(
Seq INT IDENTITY,
Item VARCHAR(MAX))
;WITH Liang AS(
SELECT
id=ROW_NUMBER() OVER(ORDER BY sys.objects.object_id)
FROM sys.columns,sys.objects)
,Liang2 AS(
SELECT
SUBSTRING(@ArrayString,id,
CHARINDEX(@Delimiter,@ArrayString+@Delimiter,id)-id) AS Item
FROM Liang AS c
WHERE SUBSTRING(@Delimiter+@ArrayString,id,1)=@Delimiter)
INSERT @t(Item)
SELECT
Item
FROM Liang2
RETURN(
SELECT
Seq AS [Seq],
Item AS [Item]
FROM @t
FOR XML PATH('element'),ROOT('row'))
END
GO
DECLARE @doc VARCHAR(MAX)
SET @doc=' '
SELECT @doc=STUFF(@doc,14,0,data)
FROM(
SELECT
dbo.array(col,',') AS data
FROM T
) AS A
CREATE TABLE #TT(ID INT IDENTITY,doc XML)
INSERT #TT(doc) VALUES(CONVERT(XML,@doc))
DECLARE @m INT
SET @m=(SELECT doc.value('max(/stringarray/row/element/Seq)','INT')
FROM #TT)
DECLARE @i INT
SET @i=1
DECLARE @SQL VARCHAR(MAX)
SET @SQL=''
WHILE @i<=@m
BEGIN
SET @SQL=@SQL+',T.x.value(''(./element/Item)['+CAST(@i AS VARCHAR)+']'',''varchar(50)'')'
SET @i=@i+1
END
SET @SQL=STUFF(@SQL,1,1,'')
EXEC('
SELECT '+@SQL+'
FROM #TT AS A
CROSS APPLY A.doc.nodes(''/stringarray/row'') AS T(x)
')
GO
DROP TABLE #TT
DROP TABLE T
DROP FUNCTION dbo.array
December 19, 2008 at 11:42 pm
---------------------------------
-- Author: liangCK ??
-- Date : 2008-11-14 19:03:48
---------------------------------
--> ??????: #tb
CREATE TABLE #tb(col VARCHAR(50))
INSERT INTO #tb
SELECT 'aa,bb,cc' UNION ALL
SELECT 'AAA,BB' UNION ALL
SELECT 'AAA'
--SQL????:
DECLARE @SQL VARCHAR(8000)
DECLARE @i INT,@m INT
SET @SQL=''
SET @m=(SELECT MAX([length])+1
FROM(
SELECT (LEN(col)-LEN(REPLACE(col,',','')))/LEN(',') AS [length]
FROM #tb) AS A)
SET @i=1
WHILE @i<=@m
BEGIN
SET @SQL=@SQL+',MAX(CASE WHEN cid='+CAST(@i AS VARCHAR)+' THEN data ELSE '''' END) AS col'+CAST(@i AS VARCHAR)
SET @i=@i+1
END
EXEC('
WITH Liang AS
(
SELECT
col,
rid=ROW_NUMBER() OVER(ORDER BY GETDATE())
FROM #tb)
,Liang2 AS
(
SELECT
A.rid,
A.col,
B.data,
cid=ROW_NUMBER() OVER(PARTITION BY A.rid ORDER BY B.data)
FROM(
SELECT
col,
rid,
v=CONVERT(XML,'' '')
FROM Liang) AS A
CROSS APPLY(
SELECT
x.value(''.'',''varchar(50)'') AS data
FROM A.v.nodes(''//v'') AS T(x)) AS B)
SELECT col'+@SQL+'
FROM Liang2
GROUP BY col')
DROP TABLE #tb
/*
col col1 col2 col3
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
aa,bb,cc aa bb cc
AAA AAA
AAA,BB AAA BB
(3 ????)
*/
December 20, 2008 at 5:28 am
rog pike (12/19/2008)
Jeff Moden (12/19/2008)
Heh... Just what you were hoping for, huh, Steve?That strikes me as an interesting question. I'm not sure if it's the case that right now I don't have an articulate answer or I'm too embarrassed to write it. Gonna have to get back to you on that one ;):)
Nah... no need. With all the posts that you've placed about RAC, I thought it a bit ironic that someone is asking about free copies.
--Jeff Moden
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply