Make Rows As Columns

Question

Make Rows As Columns

moayad-alsaleh

Grasshopper

Points: 18
More actions
February 20, 2012 at 2:14 am

#264767

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

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

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

Divine Flame SSCoach Points: 15943 More actions · Answer 1

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

moayad-alsaleh Grasshopper Points: 18 More actions · Answer 2

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

guptaanshulcse Grasshopper Points: 12 More actions · Answer 3

guptaanshulcse

Grasshopper

Points: 12

February 21, 2012 at 12:23 am

#1449498

Use Pivot table in sql

Divine Flame SSCoach Points: 15943 More actions · Answer 4

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

cengland0 SSCertifiable Points: 6102 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 6

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)

cengland0 SSCertifiable Points: 6102 More actions · Answer 7

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

CapnHector SSCoach Points: 16143 More actions · Answer 8

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]

cengland0 SSCertifiable Points: 6102 More actions · Answer 9

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.

CapnHector SSCoach Points: 16143 More actions · Answer 10

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.