GhantaBro
Posting Yak Master
215 Posts |
Posted - 2010-07-20 : 11:35:08
|
Hi Guys,I am trying to export data into text files based on the account number... the query runs fine, but when I use Pivot and Unpivot to export data in vertical, the export doesn't work and it doesnt fail either... What I am doing is based on Account Number, I am creating separate files and the data has to be exported vertically. It is running correctly with simple horizontal select... BCP doesn't like pivot/unpivot seems like... any experience with such issue? Thanks for taking time to help me on this.--Sample--I had to create table with same datatype and size for pivotCreate table Bank_Data_Source(captr_dt varchar(50) ,acct_nbr varchar(50),amt varchar(50),serial_nbr varchar(50), dr_cr_cd varchar(50), [desc] varchar(50), eor varchar(50)) Insert into Bank_Data_SourceSelect '7/12/2008', '909878789', '20.50', '1223', 'C', 'Bank deposit', '^'union allSelect '7/14/2008', '909878789', '20.50', '1223', 'C', 'Bank deposit', '^'union allSelect '7/12/2008', '888988788', '200.50', '0', 'C', '', '^'union allSelect '7/14/2008', '888988788', '120.50', '234', 'D', 'paid to dr...', '^'DECLARE @FileName varchar(500), @bcpCommand varchar(4000), @ACCT_NBR varchar(255), @i int DECLARE @keepDifferentAccts table (ACCT_NBR varchar(255), IsDone char(1)) insert @keepDifferentAccts select cast(ACCT_NBR as varchar(255)) , 0 from Bank_Data_Source group by ACCT_NBR --select * from @keepDifferentAcctsselect @i = count(*) from @keepDifferentAccts where IsDone = '0'while @i > 0 begin select top 1 @ACCT_NBR= cast(ACCT_NBR as varchar) from @keepDifferentAccts where IsDone=0 SET @FileName = REPLACE('e:\Acct_' + @ACCT_NBR+ '_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','_') SET @bcpCommand = ' bcp "select colVal from (select' + '''D''' + ' + cast(ltrim(rtrim(captr_dt)) as varchar) captr_dt,' + '''N''' + ' + cast(ltrim(rtrim(acct_nbr)) as varchar) as acct_nbr ,' + '''T''' + ' + cast(case when LTRIM(RTRIM(dr_cr_cd)) = ' + '''D''' + ' then ' + '''-''' + ' + CAST(AMT as varchar) else ltrim(rtrim(AMT)) end as varchar) as amt, '+ '''N''' + ' + cast(LTRIM(rtrim(SERIAL_NBR)) as varchar) as serial_nbr, ' + '''L''' + ' + cast(LTRIM(rtrim(dr_cr_cd)) as varchar) as dr_cr_cd, ' + '''P''' + ' + CAST( LTRIM(rtrim([Desc])) as varchar) as [desc], ' + '''''' + ' + CAST(ltrim(rtrim(EOR)) as varchar) as eor from Bank_Data_Source where acct_nbr = ' + '''' + @ACCT_NBR + '''' + ') pivot_table unpivot ( ColVal for Rows in (captr_dt ,acct_nbr ,amt ,serial_nbr, dr_cr_cd, [desc], eor )) pivot_handle' +'" queryout "' SET @bcpCommand = @bcpCommand + @FileName + '" -T -c' select @bcpCommand EXEC master..xp_cmdshell @bcpCommand update @keepDifferentAccts set IsDone='1' where ACCT_NBR = @ACCT_NBR set @i=@i - 1end |
|