none
SQL Query RRS feed

  • Question

  • I have value below like this in my SQL Table

    UserID   Name   ECode  Edescription   Keys     Date

    23           Raja      1         KeyTaken           11       08/04/10  10:40 AM

    23           Raja      1         Key Taken          11       08/04/10  11:05 AM

    23           Raja      2         Key Return         11       08/04/10  12:10 AM

    23           Raja      2         Key Return         11       08/04/10  12:25 AM

    I want output like below, please anybody give me query 

    UserID   Name    Edescription     Keys  Taken Time             Edescription     Keys       Return Time

    23           Raja      Key Taken          11      08/04/10  10:40 AM     Key Return        11          08/04/10  12:10 AM

    23           Raja      Key Taken          11      08/04/10  11:05 AM     Key Return        11          08/04/10  12:25 AM

     

    Please any one can help me to do this.

    Govind

     

     

     

    Friday, August 6, 2010 9:35 AM

Answers

  • Thank you, I'm very kind of you, it is really helpful to me. please give me some example and explanation of Over(partition by

    The OVER() clause in SQL Server has two components: PARTITION BY and ORDER BY. The first determines the grouping, and is akin to the GROUP BY clause. The ORDER BY clause determines the order with the group.

    Here is a query that illustrates:

    SELECT type, name,
           row_number() OVER (PARTITION BY type ORDER BY name)
    FROM   sys.objects
    ORDER  BY type, name

    Note that the row numbering starts with each new type.

    Note also that you need the ORDER BY clause at the end, to get the result presented in this order. The OVER() clause apply to the row-numbering only.

    Beside row_number, you can use OVER() in this form with the rank(), dense_rank() and ntile() functions. rank() and dense_rank() are similar to row_number, but rows with the same value in the ORDER BY column, will get the same rank.

    You can also use OVER() with aggregate functions like SUM() or MAX(), but in this case, you can only use PARTITION BY, not ORDER BY.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by KJian_ Thursday, August 12, 2010 10:13 AM
    Saturday, August 7, 2010 9:02 AM

All replies

  • I would do this with a self join like this (untested):

    select
    a.UserID,
    a.Name,
    a.Edescripion as TakenEdescripion,
    a.keys as TakenKeys,
    a.date as Takentime,
    b.Edescription as ReturnEdescrition,
    b.keys as ReturnKeys, b.Date as Returmtime
    from Table a inner join table b on -- maybe left join
    a.UserID = b.UserID and
    a.ECode = 1 and
    b.Ecode = 2

    Friday, August 6, 2010 9:44 AM
  • Hmm,

    I am just wondering, what the exact data is in these tables. The ECode is unic for each borrowing of a key? Then it could be something like:

    SELECT t1.UserId, t1.Name, t1.Edescription, t1.Keys, t1.Date [Taken Time], t2.Edescription, t2.Keys, t2.Date [Return Time]
    FROM MyTable t1
    LEFT JOIN MyTable t2 ON t1.Ecode = t2.Ecode
    WHERE t1.Edescription = 'KeyTaken' AND t2.Edescription = 'Key Return'

    I would have that as a first start. I just entered it here and I hope that I did not typing mistake. And I hope that I didn't mix right and left join. That is important so you see keys taken that are not returned so far.

    With kind regards,

    Konrad

    EDIT: Hmm, I saw the other answer and I got the Ecode wrong. But what I am missing is a key to see which belongs to each other. You get a problem in the case a User borrows a key, gives it back and then borrows it again!

     

    Friday, August 6, 2010 9:48 AM
  • Yes, Konrad, your right. I didn't see this missing key. My code will bring a Cartesion Product, I suspect.

    I told, I didn't test ;-)

    Friday, August 6, 2010 9:56 AM
  • Same here. But we got more or less the same idea with simply joining the table another time and I hope that this was the missing point.

    And I hope, that the data was simplified a little so there is a way to put the key taken together with the right key return.

    (If that is not the case and the "Keys" must only match (e.g. keys 11 is set of keys nr. 11 and that can only be taken once), then you could even build up a cursor and go through the table to set up a new table. But I wouldn't recommend that. Instead of a cursor workaround I would try to change the database design.)

    With kidn regards,

    Konrad

    Friday, August 6, 2010 10:04 AM
  • Thank you for your reply.

    It will display many rows I need, I want it should not display many rows.

    K. Govindaraj

     

     

    Friday, August 6, 2010 11:02 AM
  • Thank you for your reply.

    It will display many rows I need, I want it should not display many rows.

    K. Govindaraj

    Friday, August 6, 2010 11:02 AM
  • As Konrad said right, there is a missing key which row "Key Taken" corresponds with which row "Key Return".

    You hav two rows "Key Taken" and two rows "Key Return", but which belongs to which?

    If always the first "taken" belongs to the first "Return" you can add a rownumber (not tested).

    with cte as (
    select UserID,   Name,   ECode,  Edescription,   Keys,     Date,
    row_number() over (partition by userID, ECode order by date) as rn
    from table
    )

    select
    a.UserID,
    a.Name,
    a.Edescription as TakenEdescripion,
    a.keys as TakenKeys,
    a.date as Takentime,
    b.Edescription as ReturnEdescrition,
    b.keys as ReturnKeys, b.Date as Returmtime
    from cte a inner join cte b on -- maybe left join
    a.UserID = b.UserID and
    a.rn = b.rn and
    a.ECode = 1 and
    b.Ecode = 2

    • Edited by Christa Kurschat Friday, August 6, 2010 11:30 AM correct some typing errors
    Friday, August 6, 2010 11:22 AM
  • This is the solution , tested with a test data and script of the table is also given

    CREATE TABLE [dbo].[table](
     [userid] [int] NOT NULL,
     [name] [varchar](50) NULL,
     [ecode] [varchar](50) NULL,
     [description] [varchar](50) NULL,
     [keys] [varchar](50) NULL,
     [date] [datetime] NULL
    ) ON [PRIMARY]

    select *
    ,ROW_NUMBER() over (partition by ecode order by date) row_num
    into #keytaken
    from [Table]
    where ecode=1

    select *
    ,ROW_NUMBER() over (partition by ecode order by date) row_num
    into #keygiven
    from [Table]
    where ecode=2

    select kt.userid,kt.name,kt.description,kt.keys,kt.DATE takentime,kg.description,kg.keys,kg.date returntime
    from #keytaken kt inner join #keygiven kg on kt.row_num=kg.row_num

    • Proposed as answer by Muhammad Abbas Friday, August 6, 2010 11:34 AM
    Friday, August 6, 2010 11:34 AM
  • I doubt it. Your example just worked because you have everything in the right order. But maybe the order is not fixed!

    So just take the case:

    User A gets key1
    User B hets Key2
    User A gets Key3
    User A gives back key3
    User A gives back key1
    User B gives back key2

    Nor you build up to tables with rownumbers:
    First Table will have
    1) User A - key1
    2) User B - key2
    3) user C - key3

    And wnd Table:
    1) User A - key3
    2) User A - Key1
    3) User B - key2

    And now you set it together row by row -So User A gets key1 with user A returns key3  ...

    So I doubt that it can work that easily without a better database design. (And maybe some more explanations. what the data really means..)


    So I don't think that your anwer is proposed as answer. (I would double-Check my answer before clicking myself on that status :) )

    Maybe it is possible to use the original query and to each key get the next return date (e.g. lowest rownumber with rownumber higher than the rownumber of the disposal. Together with a check that the username is the same oder the key is the same ... but can we assume that? Maybe it is possible, that I get a key give it to someone else and that person hands it back? All we do is some guessing. And from all guessing we do not get a clean answer in my eyes.

    With kind regards,

    Konrad

    Friday, August 6, 2010 12:04 PM
  • Dear

     

    Thank you, I'm very kind of you, it is really helpful to me. please give me some example and explanation of Over(partition by

    Thank you

     

    K.Govindaraj

     

     

    Saturday, August 7, 2010 7:10 AM
  • Thank you, I'm very kind of you, it is really helpful to me. please give me some example and explanation of Over(partition by

    The OVER() clause in SQL Server has two components: PARTITION BY and ORDER BY. The first determines the grouping, and is akin to the GROUP BY clause. The ORDER BY clause determines the order with the group.

    Here is a query that illustrates:

    SELECT type, name,
           row_number() OVER (PARTITION BY type ORDER BY name)
    FROM   sys.objects
    ORDER  BY type, name

    Note that the row numbering starts with each new type.

    Note also that you need the ORDER BY clause at the end, to get the result presented in this order. The OVER() clause apply to the row-numbering only.

    Beside row_number, you can use OVER() in this form with the rank(), dense_rank() and ntile() functions. rank() and dense_rank() are similar to row_number, but rows with the same value in the ORDER BY column, will get the same rank.

    You can also use OVER() with aggregate functions like SUM() or MAX(), but in this case, you can only use PARTITION BY, not ORDER BY.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Marked as answer by KJian_ Thursday, August 12, 2010 10:13 AM
    Saturday, August 7, 2010 9:02 AM