June 10, 2004 at 4:55 pm
OK, I know one of you SQL Gurus will be able to figure this out but I can't seem to come up with the answer. I am trying to do this in a stored procedure without using a cursor. All help is appreciated.
Here is a sample input dataset from my source table:
Field1 Field2 Field3
AR Fay Bob
AR Fay John
AR Fay Steve
AR Fay Chris
AR Fay Angie
AR Fay David
AR Fay Jenny
TX Dal Arnold
TX Dal Jim
TX Dal Kim
TX Dal Tom
OK OKC Ron
I have a table created with the following code:
CREATE TABLE MyTable(
State char(2),
Town char(3),
Name1 varchar(20),
Name2 varchar(20),
Name3 varchar(20))
I want to take the sample input data and put it into MyTable so the output will look like the following:
State Town Name1 Name2 Name3
AR Fay Bob John Steve
AR Fay Chris Angie David
AR Fay Jenny NULL NULL
TX Dal Arnold Jim Kim
TX Dal Tom NULL NULL
OK OKC Ron NULL NULL
Remember, I want to do this in a stored procedure without cursors.
Thanks in advance, guys!
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
June 11, 2004 at 7:52 am
First off, this is probably a really bad idea. However, if you still want to do it, then so be it. Denormalization of data causes a number of anamolies and probably will cause you more harm that good in the long run. If you think it helps with performance, or simplicity, it really does neither.
Secondly, how do you determine HOW these names are put together when you have 7 that are going.
Just in the order you ahve them? No order by clause to set it? With that information I could probably get you in the ball park, but until I understand your order, I can't help.
Let me re-emphasize that this implementation is probably not a good idea.
June 11, 2004 at 7:58 am
I understand the problem with de-normalization. I am not putting this in a permanent table. I am putting this in a temp table in the SP so I can pull the data as a data set for a report being built in MS Reporting Services. This is the best format due to the way they want the report formatted.
Let's assume the order of the names is just alphabetical.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
June 11, 2004 at 8:08 am
Hi
I did the following ,
create table Samplein(
F1 char(2),
F2 varchar(10),
F3 varchar(10)
)
inserted records
select * from Samplein
F1 F2 F3
---- ---------- ----------
AR Fay Bob
AR Fay John
AR Fay Steve
AR Fay Chris
TX Dal Arnold
TX Dal Jim
TX Dal Kim
CREATE TABLE MyTable(
State char(2),
Town char(3),
Name1 varchar(20),
Name2 varchar(20),
Name3 varchar(20))
declare @var1 varchar(25)
declare @var2 varchar(25)
declare @var3 varchar(25)
declare @var4 varchar(25)
declare @var5 varchar(25)
select top 1 @var1= F1, @var2= F2 from SampleIN
select top 1 @var3 = F3 from SampleIN
select top 2 @var4 = F3 from SampleIN
select top 3 @var5 = F3 from SampleIN
print @var1
print @var2
print @var3
print @var4
print @var5
Insert into MyTable values (@var1, @var2, @var3, @var4,@var5)
select * from MyTable
State Town Name1 Name2 Name3
----- ---- -------------------- -------------------- --------------------
AR Fay Bob John Steve
Now to put same inside a stored proc
i did the following
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'LoadSample' AND type = 'P')
DROP PROCEDURE LoadSample
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'MyTable' AND Xtype = 'U')
TRUNCATE Table MyTable
GO
Create procedure LoadSample
As
declare @cnter varchar(15)
declare @reccount int
declare @SelStr1 varchar(200)
declare @SelStr2 varchar(200)
declare @insStr1 varchar(256)
declare @insStr2 varchar(100)
DECLARE @var1 nvarchar(30)
SELECT @var1 = 'Generic Name'
declare @var2 varchar(25)
declare @var3 varchar(25)
declare @var4 varchar(25)
declare @var5 varchar(25)
set @cnter = 1
set @SelStr1 = ''
set @SelStr2 = ''
set @insStr1 = ''
set @insStr2 = ''
set @var1 =''
set @var2 =''
set @var3 =''
set @reccount = (Select count(*) from SampleIN)
While (@cnter <= @reccount)
BEGIN
Exec('Select top ' + @cnter + ' @var1 = F1 , @var2 = F2 from SampleIN ')
Exec('Select top ' + @cnter + ' @var3 = F3 from SampleIN ')
set @cnter = @cnter + 1
Exec('Select top ' + @cnter + ' @var4 = F3 from SampleIN ')
set @cnter = @cnter + 1
Exec('Select top ' + @cnter + ' @var5 = F3 from SampleIN ')
Insert into MyTable values (@var1, @var2, @var3, @var4,@var5)
Select * from MyTable
END
Please note : Since i am trying to assign dynamically built SQL statement to another variable and then trying to use EXEC (SQLSTR), the variable inside the SQLSTR goes out of scope, so the compiler throws an error saying @var1 must be declare dand son for rest of the variables... I am hoping somebody on this forum will be able to improve this proc.
Best Regards
THNQDigital
June 11, 2004 at 10:01 am
Because of time constraints and the limited size of my dataset, (against my wishes) I went ahead and built the SP using cursors. It works fast and the amount of data in this system should never get huge.
THNQDigital, thanks for the feedback, it just didn't capture all scenarios.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply