SQL Code Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Code Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2014-12-18 : 17:35:09

Hi Guys,

Need help with SQL Code, I am sure, it will done through Cursor or while loop.(Please correct me or guide me, if there is easiest way).

Here is my Source data (As an example)

ID,Client,TxDate,GrossCost
1,abc,11/10/2014,$10.07
2,Dest,11/10/2014,$10.07
3,Dest,11/10/2014,$10.07
4,Dest,11/10/2014,$10.07
5,xyz,11/10/2014,$10.07
6,abc,11/10/2014,$10.07

First requirement is from source file exclude all Client where Client = Dest
Second Step I have list of Clients (Len,ghi,tab)

My requirement is in the source file when client = Dest create a Same duplicate record for other three clients (Len,ghi,tab)

Here is my final final should looks lik.

ID,Client,TxDate,GrossCost
1,abc,11/10/2014,$10.07
2,Dest,11/10/2014,$10.07
2,Len,11/10/2014,$10.07
2,ghi,11/10/2014,$10.07
2,tab,11/10/2014,$10.07
3,Dest,11/10/2014,$10.07
3,Len,11/10/2014,$10.07
3,ghi,11/10/2014,$10.07
3,tab,11/10/2014,$10.07
4,Dest,11/10/2014,$10.07
4,Len,11/10/2014,$10.07
4,ghi,11/10/2014,$10.07
4,tab,11/10/2014,$10.07
5,xyz,11/10/2014,$10.07
6,abc,11/10/2014,$10.07

Please let me, if my question is not clear.
Please its urgent.

Thank You.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 17:52:50

I'm sure there's a better way, but here you go:

create table #t (ID tinyint, Client varchar(5), TxDate date, GrossCost decimal(18,2))

create table #c (Client varchar(5))

insert into #c values ('Dest'), ('Len'), ('ghi'), ('tab')

insert into #t values
(1, 'abc', '11/10/14', 10.07),
(2, 'Dest', '11/10/14', 10.07),
(3, 'Dest', '11/10/14', 10.07),
(4, 'Dest', '11/10/14', 10.07),
(5, 'xyz', '11/10/14', 10.07),
(6, 'abc', '11/10/14', 10.07)

select #t.ID, #c.Client, #t.TxDate, #t.GrossCost
from #t
cross join #c -- on #t.Client = #c.Client
where #t.Client = 'Dest'
union all
select #t.ID, #t.Client, #t.TxDate, #t.GrossCost
from #t
where #t.Client <> 'Dest'
order by 1

drop table #t, #c

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-12-19 : 00:50:26

May this cursor work for you


CREATE TABLE #table(ID int,Client varchar(1024),TxDate varchar(1024),GrossCost varchar(1024))

INSERT INTO #table
VALUES
(1,'abc','11/10/2014','$10.07'),
(2,'Dest','11/10/2014','$10.07'),
(3,'Dest','11/10/2014','$10.07'),
(4,'Dest','11/10/2014','$10.07'),
(5,'xyz','11/10/2014','$10.07'),
(6,'abc','11/10/2014','$10.07')


DECLARE @procName varchar(500)
DECLARE @TxDate varchar(500)
DECLARE @GrossCost varchar(500)
DECLARE cur cursor 

FOR SELECT ID,TxDate,GrossCost FROM #table  WHERE Client = 'Dest'
OPEN cur
FETCH NEXT FROM cur INTO @procName,@TxDate,@GrossCost
WHILE @@fetch_status = 0
BEGIN
	INSERT INTO #table VALUES (@procName,'Len',@TxDate,@GrossCost)
	INSERT INTO #table VALUES (@procName,'ghi',@TxDate,@GrossCost)
	INSERT INTO #table VALUES (@procName,'tab',@TxDate,@GrossCost)
    FETCH NEXT FROM cur INTO @procName,@TxDate,@GrossCost
END
CLOSE cur
DEALLOCATE cur

SELECT * FROM #table ORDER BY id,Client ASC

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2014-12-19 : 09:29:02

[code]
DECLARE @Table TABLE(ID INT,Client VARCHAR(5),TxDate DATE,GrossCost VARCHAR(10))

INSERT INTO @Table
VALUES
(1,'abc','11/10/2014','$10.07'),
(2,'Dest','11/10/2014','$10.07'),
(3,'Dest','11/10/2014','$10.07'),
(4,'Dest','11/10/2014','$10.07'),
(5,'xyz','11/10/2014','$10.07'),
(6,'abc','11/10/2014','$10.07')

select *
from @table t1
union
select ID,a.NewClient,TxDate,grossCost
from @table t1
CROSS JOIN
( VALUES
('LEN'),
('ghi'),
('tab')
) a(NewClient)

where t1.Client = 'dest'
order by ID

jim
[/code]

Everyday I learn something that somebody else already knew

tooba
Posting Yak Master

224 Posts

Posted - 2014-12-22 : 16:59:29

Thank You All.

Looks good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources