query help

  • 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

  • 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/

  • 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.

  • 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

  • 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

  • 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.

  • 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

  • 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/

  • 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/

  • 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