April 22, 2013 at 7:48 am
Create table Mytemp
(Id int ,
value nvarchar(100),
name varchar(50))
insert into [rbamouser\parAPatg].MYTEMP VALUES(1,'B,C,D','XYZ')
mY O/P SHOULD BE LILKE
1 B XYZ
1 C XYZ
1 D XYZ
KKINDLY REQUEST YOU TO HELP ME IN THIS REGARD
April 22, 2013 at 8:01 am
The real problem you are facing is that your data is not normalized. You are storing multiple values in a single column. By far the best thing you could do would be to fix the normalization issues. If you can't do that then you will have to parse the values. About the best way to do that is using the DelimitedSplit8K function. You can read about that (and find the code for creating it) by following the link in my signature about splitting strings.
Once you have that function (and understand how it works) you can retrieve your data in the format you described like this:
select ID, Item, name
from mytemp
cross apply dbo.DelimitedSplit8K(value, ',')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 8:06 am
Hi, does this help?
CREATE TABLE #Mytemp
(Id INT ,
value CHAR(1),
name CHAR(3))
INSERT INTO #MYTEMP
SELECT 1,'B','XYZ'
UNION ALL
SELECT 1,'C','XYZ'
UNION ALL
SELECT 1,'D','XYZ'
SELECT * FROM #MYTEMP
DROP TABLE #MYTEMP
April 22, 2013 at 8:17 am
use a tally table.
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
declare @parameter varchar(100)
select @parameter = value from Mytemp
SELECT (select Id from Mytemp) as [Id],SUBSTRING(@parameter,N,1) as [value], (select name from Mytemp) as [name]
FROM #Tally
WHERE N <= LEN(@parameter)
and SUBSTRING(@parameter,N,1) != ','
April 22, 2013 at 8:24 am
Geoff A (4/22/2013)
use a tally table.
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
declare @parameter varchar(100)
select @parameter = value from Mytemp
SELECT (select Id from Mytemp) as [Id],SUBSTRING(@parameter,N,1) as [value], (select name from Mytemp) as [name]
FROM #Tally
WHERE N <= LEN(@parameter)
and SUBSTRING(@parameter,N,1) != ','
That will work until you have a second row in the base table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 8:30 am
yes, i know. just wanted to throw out a tally table solution too.
April 22, 2013 at 8:34 am
Geoff A (4/22/2013)
yes, i know. just wanted to throw out a tally table solution too.
It does seem to beat the DelimitedSplit8K version almost every time.
SET NOCOUNT ON;
declare @parameter varchar(100)
select @parameter = value from Mytemp
PRINT '==========DelimitedSplit8K========'
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
select ID, Item, name
from mytemp
cross apply dbo.DelimitedSplit8K(value, ',')
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '==========Tally========'
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT (select Id from Mytemp) as [Id],SUBSTRING(@parameter,N,1) as [value], (select name from Mytemp) as [name]
FROM #Tally
WHERE N <= LEN(@parameter)
and SUBSTRING(@parameter,N,1) != ','
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
Here are my results:
==========DelimitedSplit8K========
Table 'Mytemp'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 49 ms.
================================================================================
==========DelimitedSplit8K========
Table 'Mytemp'. Scan count 6, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Tally______________________________________________________________________________________________________________000000000003'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 41 ms.
================================================================================
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 10:00 am
Remember the warning about using statistics to measure performance of functions. Although iTVFs fair better in this area than scalar functions, they're still affected. The best way to measure performance when any type of userdefined function is used is to use an SQL Profiler run and make sure that you're not returning results of the query to the screen because that takes a toll, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2013 at 10:41 am
You can also use this as a test harness:
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
--< Code to test goes here >--
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
April 22, 2013 at 10:49 am
Lynn Pettis (4/22/2013)
You can also use this as a test harness:
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
--< Code to test goes here >--
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
Using this method the splitter beats the tally version nearly 2:1 which is more of what I would have expected.
CPU(ms): 0 Logical Reads: 69 Elapsed(ms): 4 Reads: 14 Writes: 0
CPU(ms): 0 Logical Reads: 115 Elapsed(ms): 4 Reads: 8 Writes: 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 10:53 am
Sean Lange (4/22/2013)
Lynn Pettis (4/22/2013)
You can also use this as a test harness:
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
--< Code to test goes here >--
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
Using this method the splitter beats the tally version nearly 2:1 which is more of what I would have expected.
CPU(ms): 0 Logical Reads: 69 Elapsed(ms): 4 Reads: 14 Writes: 0
CPU(ms): 0 Logical Reads: 115 Elapsed(ms): 4 Reads: 8 Writes: 0
Looks like a tie elapsed time wise:
CPU(ms): 0 Logical Reads: 69 Elapsed(ms): 4 Reads: 14 Writes: 0
CPU(ms): 0 Logical Reads: 115 Elapsed(ms): 4 Reads: 8 Writes: 0
April 22, 2013 at 10:55 am
Lynn Pettis (4/22/2013)
Sean Lange (4/22/2013)
Lynn Pettis (4/22/2013)
You can also use this as a test harness:
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
--< Code to test goes here >--
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
Using this method the splitter beats the tally version nearly 2:1 which is more of what I would have expected.
CPU(ms): 0 Logical Reads: 69 Elapsed(ms): 4 Reads: 14 Writes: 0
CPU(ms): 0 Logical Reads: 115 Elapsed(ms): 4 Reads: 8 Writes: 0
Looks like a tie elapsed time wise:
CPU(ms): 0 Logical Reads: 69 Elapsed(ms): 4 Reads: 14 Writes: 0
CPU(ms): 0 Logical Reads: 115 Elapsed(ms): 4 Reads: 8 Writes: 0
LOL right you are!!! I was misreading the output. I am going to back to work now and forgot about SSC for today. I am obviously having a Monday!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 11:08 am
and to the original concern about what to do if more rows came into the mix, i would try something along these lines;
Create table #Mytemp
(Id int ,
value nvarchar(100),
name varchar(50))
insert into #Mytemp VALUES(1,'B,C,D','XYZ'),
(2,'E,F,G','QWE'),
(3,'H,I,J','ASD')
SELECT TOP 1000
IDENTITY(INT,1,1) AS N
INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
declare @cols varchar(max)
select @cols = coalesce(@cols+'', '') + value FROM #Mytemp
; with CTE as (
select SUBSTRING(@cols,N,1) as [value]
FROM #Tally
WHERE N <= LEN(@cols)
and SUBSTRING(@cols,N,1) != ','
)
select T1.Id, T2.value, T1.name
from #Mytemp T1
join CTE T2
on T1.value like ('%' +T2.value+ '%')
drop table #Tally
drop table #Mytemp
April 22, 2013 at 11:24 am
hi , my name is florence , am new in sql data, l need help to be able to kow database
April 22, 2013 at 11:45 am
fadewumi (4/22/2013)
hi , my name is florence , am new in sql data, l need help to be able to kow database
Hi and welcome Florence. The forums here are a great place to learn. It is a much better practice to start your own thread instead replying to another thread with a new topic.
What I would recommend is to establish a test environment where you can try all sorts of things without impacting any real systems. Download and install SQL Express if you don't have a copy of sql server.
Then try to figure out what areas you want to start with. Hang out on the forums here. Try to answer questions when you feel comfortable. Ask questions of your own.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply