March 8, 2013 at 11:11 am
Hi All,
can any one help on below query
declare @tbl table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int)
insert @tbl(ID,patientName,age,city,Mobile) select 1,'Ramesh',20,'HYD',12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,87654321
select * from @tbl
TBLID ID patientName age city Mobile
1 1 Ramesh 20 HYD 12345678
2 1 Ramesh 24 HYD 12345678
3 1 Ramesh new20 HYDERABAD 87654321
i want output as mentioned below format which columns data got changed
Columns OLDDATA NEWDATA
patientName Ramesh Ramesh new
City HYD HYDERABAD
Mobile 12345678 87654321
please prepare any dynamic query
March 8, 2013 at 12:42 pm
Well you did a pretty decent job of posting ddl and sample data. However it is totally unclear what you want for output. What column in your table defines the order?
_______________________________________________________________
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/
March 8, 2013 at 12:56 pm
Are you looking for something like OUTPUT? http://msdn.microsoft.com/en-us/library/ms177564.aspx
I know in cases when you run an UPDATE you can use this to view the deleted.fieldname (old version), inserted.fieldname (new version) etc.
March 8, 2013 at 11:08 pm
declare @tbl table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int)
insert @tbl(ID,patientName,age,city,Mobile)
select 1,'Ramesh',20,'HYD',12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,87654321
select * from @tbl
TBLID, ID, patientName , age , city , Mobile
1 , 1 , Ramesh , 20 , HYD , 12345678
2 , 1 , Ramesh , 24 , HYD , 12345678
3 , 1 , Ramesh new, 20 , HYDERABAD , 87654321
i want output as mentioned below format which columns data got changed
input params @FromTBLID,@ToTBLID
if i will pass @FromTBLID=1,@ToTBLID=3 then it will display like below
Columns, OLDDATA , NEWDATA
patientName ,Ramesh ,Ramesh new
City ,HYD , HYDERABAD
Mobile ,12345678 ,87654321
if i will pass @FromTBLID=1,@ToTBLID=2 then it will display like below
Columns, OLDDATA , NEWDATA
age , 20 ,24
March 8, 2013 at 11:09 pm
declare @tbl table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int)
insert @tbl(ID,patientName,age,city,Mobile)
select 1,'Ramesh',20,'HYD',12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,87654321
select * from @tbl
TBLID, ID, patientName , age , city , Mobile
1 , 1 , Ramesh , 20 , HYD , 12345678
2 , 1 , Ramesh , 24 , HYD , 12345678
3 , 1 , Ramesh new, 20 , HYDERABAD , 87654321
i want output as mentioned below format which columns data got changed
input params @FromTBLID,@ToTBLID
if i will pass @FromTBLID=1,@ToTBLID=3 then it will display like below
Columns, OLDDATA , NEWDATA
patientName ,Ramesh ,Ramesh new
City ,HYD , HYDERABAD
Mobile ,12345678 ,87654321
if i will pass @FromTBLID=1,@ToTBLID=2 then it will display like below
Columns, OLDDATA , NEWDATA
age , 20 ,24
please Create query for above requirement
March 9, 2013 at 6:11 am
How about you provide us with the code you have written so far in an attempt to solve your problem.
We are volunteers on this site, willing to help you in resolving you problem. We aren't here to do your work for you.
March 18, 2013 at 11:48 am
here i am doing like dis
declare @tbl1 table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int)
insert @tbl1(ID,patientName,age,city,Mobile) select 1,'Ramesh',20,'HYD',12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,12345678 union all
select 1,'Ramesh new',20,'HYDERABAD ' ,87654321
select * into #temp from @tbl1
select name into #Result from tempdb..syscolumns where id=OBJECT_ID('tempdb..#temp')
alter table #Result add oldvalue varchar(100), newvalue varchar(100)
declare @old varchar(max),@New varchar(max),@column varchar(100)='name'
declare @tbl table (Value varchar(max))
declare temp cursor local for
select name from #Result
open temp
fetch next from temp into @column
while @@FETCH_STATUS=0
begin
insert @tbl exec ('select '+@column+' from #temp ')
select top 1 @old= Value from @tbl order by Value desc
select top 1 @new= Value from @tbl order by Value asc
update #Result set oldvalue=@old,newvalue=@New where name=@column
delete from @tbl
fetch next from temp into @column
end
select * from #Result where oldvalue<>newvalue
drop table #temp ,#Result
can any help me
is there any best way
March 18, 2013 at 1:05 pm
So you want to get the lowest of each value as the NewValue and the largest of each value as OldValue and then unpivot those results? Assuming that is what you want there is a decent way we can accomplish with some dynamic sql. It will at least let you get rid of that cursor.
_______________________________________________________________
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/
March 18, 2013 at 1:54 pm
This produces the same output as your example.
if OBJECT_ID('MyResult') is not null
drop table MyResult
create table MyResult
(
TBLID int identity(1,1),
ID int,
patientName varchar(10),
age int,
city varchar(100),
Mobile int
)
insert MyResult(ID, patientName, age, city, Mobile)
select 1,'Ramesh',20,'HYD',12345678 union all
select 1,'Ramesh new',20,'HYDERABAD' ,12345678 union all
select 1,'Ramesh new',20,'HYDERABAD' ,87654321
declare @sql nvarchar(max)
select @sql = COALESCE(@sql + ' union all ', '') + 'select ''' + c.name + ''' as [Column], Max(Cast([' + c.name + '] AS VarChar(MAX))) as [OldValue], Min(Cast([' + c.name + '] AS VarChar(MAX))) as [NewValue] from MyResult having Min(Cast([' + c.name + '] AS VarChar(MAX))) <> Max(Cast([' + c.name + '] AS VarChar(MAX)))'
from sys.columns c
inner join sys.objects o on c.object_id = o.object_id and o.type = 'u'
where o.object_id = object_id('MyResult')
order by c.column_id
--select @sql
exec sp_executesql @sql
_______________________________________________________________
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/
March 20, 2013 at 11:40 am
thank u so much...........
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply