Make Rows As Columns

  • Dears ,

    I have very hard View , that making the Rows to Column , but i couldn't Find the Solution for that

    i have below view which is Steps done from the Users, and this view sorted by EventTime

    so i want to Change the View to retrieve Data as New View below

    in the new view i just make it manual in table to show how it should be

    Regards,

    Moayad Al-Saleh

  • How do you identify the StartTime & EndTime for a particular event? I don't see any EventId column on the basis of which you can decide. Please provide the name of that also if it is there.


    Sujeet Singh

  • Here is the Solution :

    ;with q as(

    select ProductID, EventTime, RoleName

    , ROW_NUMBER() over (order by EventTime) rn

    , ROW_NUMBER() over (partition by ProductID, RoleName order by EventTime) rn2

    from YourTable

    )

    select ProductID, MIN(EventTime) as StartTime, MAX(EventTime) as EndTime, RoleName

    from q

    group by ProductID, RoleName, rn-rn2

    order by MIN(EventTime)

    🙂 , Amazing

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/85317604-f003-4cba-9a9f-11b234acd712

  • Use Pivot table in sql

  • guptaanshulcse (2/21/2012)


    Use Pivot table in sql

    I am not sure how he is identifying the events. I don't see any column which can be used to uniquely identify a particular event leave alone identifying the StartTime & EndTime for that event.

    However, glad to know he has find the solution.


    Sujeet Singh

  • Divine Flame (2/21/2012)


    guptaanshulcse (2/21/2012)


    Use Pivot table in sql

    I am not sure how he is identifying the events. I don't see any column which can be used to uniquely identify a particular event leave alone identifying the StartTime & EndTime for that event.

    However, glad to know he has find the solution.

    Seems he's simply getting the min to identify the start time and the max to identify the end time.

  • moayad-alsaleh (2/20/2012)


    Dears ,

    I have very hard View , that making the Rows to Column , but i couldn't Find the Solution for that

    i have below view which is Steps done from the Users, and this view sorted by EventTime

    so i want to Change the View to retrieve Data as New View below

    in the new view i just make it manual in table to show how it should be

    Regards,

    Moayad Al-Saleh

    You did a very nice job of explaining especially with the graphics. If you could take the same information you have in the graphics and post it as readily consumable data (PLEASE see the first link in my signature line below for the right way to do that), I can show you how to do this with code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Can't this be accomplished by a simple query like this:

    select

    productID,

    min(EventTime) as StartTime,

    max(EventTime) as EndTime,

    RoleName

    From [YourTableNameHere]

    Group by

    ProductID,

    RoleName

  • cengland0 (2/22/2012)


    Can't this be accomplished by a simple query like this:

    select

    productID,

    min(EventTime) as StartTime,

    max(EventTime) as EndTime,

    RoleName

    From [YourTableNameHere]

    Group by

    ProductID,

    RoleName

    That would work however there are userid's that repeat, so the min and max would be the min and max per userid not per batch. if moayad-alsaleh adds a column such as batch number your solution will work.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • capn.hector (2/23/2012)


    cengland0 (2/22/2012)


    Can't this be accomplished by a simple query like this:

    select

    productID,

    min(EventTime) as StartTime,

    max(EventTime) as EndTime,

    RoleName

    From [YourTableNameHere]

    Group by

    ProductID,

    RoleName

    That would work however there are userid's that repeat, so the min and max would be the min and max per userid not per batch. if moayad-alsaleh adds a column such as batch number your solution will work.

    I'm not grasping your issue exactly. I did not include userid in the query so the min and max would be per productid and rolename -- not userid.

  • in the sample data each role only has one userid associated with it. however the problem gets larger as more userid's are associated with each role as the min and max would be by role. so you could see the first and last time each role did something.

    if you look at blocks 1, 6, and 8 in the original data, your query would pull the min from block 1 and the max from block 8 completely missing block 6. we need a way to differentiate between blocks 1, 6, and 8 for your query to display the data how the OP wants.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • moayad-alsaleh (2/20/2012)


    Here is the Solution :

    Damn... not enough coffee. I didn't see that before my previous post. That's pretty much the way I was going to suggest... the difference between two row numbers. Works great for "repeating" groups like this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply