January 6, 2014 at 1:06 am
hello all.I have a table with this records:
linknumber history
110 46,57,89
220 50
330 22
440 10,12
I want to have this result:
rownumber linknumber history
1 110 46
2 110 57
3 110 89
4 220 50
5 330 22
6 440 10
7 440 12
and I have this function for split(camma):
ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
but I don,t know how to use this function in T-sql for my favorir result?plaese guide me how do I do this?thanks
January 6, 2014 at 3:05 am
Hi,
Try the below solution
SELECT row_number() over(order by linknumber) 'RowNumber',
linknumber,
Split.a.value('.', 'VARCHAR(100)') AS Item
FROM (SELECT linknumber,
CAST ('<M>' + REPLACE([item], ',', '</M><M>') + '</M>' AS XML) AS Item
FROM test) AS A
CROSS APPLY Item.nodes ('/M') AS Split(a)
Note : The above sql is not using the function You have provided
January 6, 2014 at 3:31 am
The splitter discussed here [/url]is shown to be far superior in performance terms to either of the two already listed oon this thread. Here's how you would use it to solve your problem:
;WITH MyTable AS (
SELECT * FROM (VALUES
(110, '46,57,89'),
(220, '50'),
(330, '22'),
(440, '10,12')
) d (linknumber, history))
SELECT
rownumber = ROW_NUMBER() OVER(ORDER BY t.linknumber, d.Item),
t.linknumber,
[history] = d.Item
FROM MyTable t
CROSS APPLY dbo.DelimitedSplit8K(history,',') d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2014 at 4:00 am
thanks alot.it works.
January 6, 2014 at 5:18 am
thanks for your reply.I create this sp with your query:
ALTER procedure [dbo].[icanSP_Convert_GetEntityDependencies]
@DocId INT,@Count int
as
begin
;WITH MyTable AS (
SELECT * FROM (select linknumber,savabegh from main2 where savabegh is not null and savabegh!=''
) c (linknumber, savabegh))
SELECT TOP(@COUNT)
rownumber = ROW_NUMBER() OVER(ORDER BY t.linknumber, d.Items),
t.linknumber,
savabegh = d.Items
FROM MyTable t
CROSS APPLY dbo.Split(savabegh,',') d
where rownumber>@DocId
but get error:Invalid column name 'rownumber'
how do i solve this error?
January 6, 2014 at 5:26 am
I don't know why you over-complicated matters so much, there were multiple nested queries for no reason, but this should work
ALTER PROCEDURE [dbo].[icanSP_Convert_GetEntityDependencies] @DocId INT,
@Count INT
AS
BEGIN
WITH t AS ( SELECT ROW_NUMBER() OVER ( ORDER BY t.linknumber, d.Items ) AS rownumber ,
t.linknumber ,
d.Items AS savabegh
FROM main2
CROSS APPLY dbo.Split(savabegh, ',') d
WHERE savabegh IS NOT NULL
AND savabegh != ''
)
SELECT TOP ( @COUNT )
rownumber ,
linknumber ,
savabegh
FROM t
WHERE rownumber > @DocId
END
Though you have a TOP without an Order By, which is asking for inconsistent results. You really should put an order by on the outer query so that you get the correct set of rows every time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2014 at 6:30 am
ROW_NUMBER() on an outer SELECT will often return the results in the ROW_NUMBER() order, which could make your query equivalent to this:
SELECT rownumber, linknumber, savabegh
FROM (
SELECT
rownumber = ROW_NUMBER() OVER(ORDER BY t.linknumber, d.Items),
t.linknumber,
savabegh = d.Items
FROM MyTable t
CROSS APPLY dbo.Split(savabegh,',') d
WHERE savabegh!=''
) d
WHERE rownumber BETWEEN @DocId AND @DocId+@COUNT
It depends, as Gail says, on what you expect of TOP without ORDER BY. You'd be surprised how sensitive TOP without ORDER BY can be to seemingly external influence.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2014 at 9:07 am
elham_azizi_62 (1/6/2014)
thanks for your reply.I create this sp with your query:ALTER procedure [dbo].[icanSP_Convert_GetEntityDependencies]
@DocId INT,@Count int
as
begin
;WITH MyTable AS (
SELECT * FROM (select linknumber,savabegh from main2 where savabegh is not null and savabegh!=''
) c (linknumber, savabegh))
SELECT TOP(@COUNT)
rownumber = ROW_NUMBER() OVER(ORDER BY t.linknumber, d.Items),
t.linknumber,
savabegh = d.Items
FROM MyTable t
CROSS APPLY dbo.Split(savabegh,',') d
where rownumber>@DocId
but get error:Invalid column name 'rownumber'
how do i solve this error?
I have to say it again, that particular split function is going to make serious sucking sounds for performance. Use the DelimitedSplit8K function that Chris provided a link to, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2014 at 9:28 am
Jeff Moden (1/6/2014)
elham_azizi_62 (1/6/2014)
thanks for your reply.I create this sp with your query:ALTER procedure [dbo].[icanSP_Convert_GetEntityDependencies]
@DocId INT,@Count int
as
begin
;WITH MyTable AS (
SELECT * FROM (select linknumber,savabegh from main2 where savabegh is not null and savabegh!=''
) c (linknumber, savabegh))
SELECT TOP(@COUNT)
rownumber = ROW_NUMBER() OVER(ORDER BY t.linknumber, d.Items),
t.linknumber,
savabegh = d.Items
FROM MyTable t
CROSS APPLY dbo.Split(savabegh,',') d
where rownumber>@DocId
but get error:Invalid column name 'rownumber'
how do i solve this error?
I have to say it again, that particular split function is going to make serious sucking sounds for performance. Use the DelimitedSplit8K function that Chris provided a link to, instead.
Elham, if you haven't used the DelimitedSplit8K function before, take the time to read up on it at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It will completely redefine your expectations when it comes to string parsing performance. If in doubt, run your test table up to 1,000,000 rows and compare the performance. A large test table tends to expose weaknesses.
January 8, 2014 at 12:09 am
thanks for your reply guys.
I create this sp:
ALTER procedure [dbo].[icanSP_Convert_GetEntityDependencies]
@DocId INT,@Count int
as
begin
;WITH MyTable AS (
SELECT * FROM (select linknumber,savabegh from main2 where savabegh is not null and savabegh!=''
) c (linknumber, savabegh))
SELECT rownumber, linknumber, savabegh
FROM (
SELECT
rownumber = ROW_NUMBER() OVER(ORDER BY t.linknumber, d.Items),
t.linknumber,
savabegh = d.Items
FROM MyTable t
CROSS APPLY dbo.Split(savabegh,',') d
WHERE savabegh!='' and savabegh is not null
) d
WHERE rownumber BETWEEN @DocId AND @DocId+@COUNT
end
this sp get this result:
rownumber main2.linknumber savabegh
1 29092 67-4
2 29092 36
3 29094 401-6
4 29095 64-6
and this is true,but I want to apply another column that exist in another table with name main1,in main1 'name' filed equall with 'savabegh' filed in main2:
main1:linknumber(int),name(varchar)
main2:linknumber(int),savabegh(varchar)
I want to have main1.linknumber that main2.savabegh=main1.name
How can I do this?thanks
January 8, 2014 at 2:08 am
elham_azizi_62 (1/8/2014)
thanks for your reply guys.I create this sp:
ALTER procedure [dbo].[icanSP_Convert_GetEntityDependencies]...
As Gail has already pointed out, you're unnecessarily overcomplicating your code. Your stored procedure is the equivalent to this:
ALTER procedure [dbo].[icanSP_Convert_GetEntityDependencies]
@DocId INT,@Count INT
AS
SELECT
d.rownumber,
d.linknumber,
d.savabegh
FROM (
SELECT
rownumber = ROW_NUMBER() OVER(ORDER BY t.linknumber, d.Item),
t.linknumber,
savabegh = d.Item
FROM main2 t
CROSS APPLY dbo.DelimitedSplit8K(savabegh,',') d
WHERE savabegh != ''
) d
WHERE rownumber BETWEEN @DocId AND @DocId + @COUNT
RETURN 0
Also, as Jeff has already pointed out:
Jeff Moden (1/6/2014)
...I have to say it again, that particular split function is going to make serious sucking sounds for performance. Use the DelimitedSplit8K function that Chris provided a link to, instead.
If you choose to ignore the advice offered by contributors to your thread, they may choose not to help you in the future.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 2:13 am
elham_azizi_62 (1/8/2014)
... I want to apply another column that exist in another table with name main1,in main1 'name' filed equall with 'savabegh' filed in main2:main1:linknumber(int),name(varchar)
main2:linknumber(int),savabegh(varchar)
I want to have main1.linknumber that main2.savabegh=main1.name
How can I do this?thanks
How do you want the output to look?
Can you set up a little sample data to test against?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 2:29 am
my favorit out put is:
rownumber main2.linknumber main2.savabegh main1.linknumber
1 29027 46-4 29015
January 8, 2014 at 3:00 am
elham_azizi_62 (1/8/2014)
my favorit out put is:rownumber main2.linknumber main2.savabegh main1.linknumber
1 29027 46-4 29015
We also need a script for the new table main1, including INSERTs to populate it with some data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 4:06 am
main1's structure:
CREATE TABLE [dbo].[main1](
[number] [int] IDENTITY(1,1) NOT NULL,
[andikator] [int] NULL,
[sdate] [varchar](11) NULL,
[tipe] [varchar](1) NULL,
[asl] [varchar](1) NULL,
[vdate] [varchar](10) NULL,
[vtime] [varchar](15) NULL,
[class] [varchar](20) NULL,
[security] [varchar](20) NULL,
[secno] [varchar](1) NULL,
[ldate] [varchar](11) NULL,
[name] [varchar](50) NULL,
[ffrom] [varchar](200) NULL,
[daryaft] [varchar](200) NULL,
[subject] [varchar](240) NULL,
[owner] [varchar](50) NULL,
[departmanno] [int] NULL,
[sabt] [varchar](1) NULL,
[subcode] [varchar](11) NULL,
[ownerlast] [varchar](50) NULL,
[emza] [varchar](50) NULL,
[peygiridat] [varchar](11) NULL,
[peygiri] [varchar](1) NULL,
[reshteh] [text] NULL,
[linknumber] [int] NULL,
[leterflag] [tinyint] NULL,
[boardtype] [tinyint] NULL,
[typeflag] [tinyint] NULL,
[file_link_number] [varchar](11) NULL,
[file_link_flag] [char](1) NULL,
PRIMARY KEY CLUSTERED
(
[number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
main2's structure:
CREATE TABLE [dbo].[main2](
[linknumber] [int] NOT NULL,
[minoteemzaownerno] [int] NULL,
[minoteemzaluck] [tinyint] NULL,
[letteremzaownerno] [int] NULL,
[letteremzaluck] [tinyint] NULL,
[karedit] [varchar](1) NULL,
[sabegheh] [varchar](1) NULL,
[savabegh] [nvarchar](max) NULL,
[reporttext] [text] NULL,
[sabat] [varchar](50) NULL,
[last] [text] NULL,
[radif] [int] NULL,
[baycode] [varchar](20) NULL,
CONSTRAINT [PK__main2__4FE2E5B033D4B598] PRIMARY KEY CLUSTERED
(
[linknumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[main2] ADD CONSTRAINT [DF__main2__minoteemz__35BCFE0A] DEFAULT ((0)) FOR [minoteemzaluck]
GO
ALTER TABLE [dbo].[main2] ADD CONSTRAINT [DF__main2__letteremz__36B12243] DEFAULT ((0)) FOR [letteremzaluck]
GO
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply