| 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. |
|
|
|
|
|
|
SQL Code Help