Convert Rows to columns so...

Question

Convert Rows to columns so...

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Heh... No matter where it's done, I'll still like to know why the BA thinks that listing an unknown number hospital rooms horizontally is the right thing to do on any system. :blink:

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

fausto usme (12/4/2008)
>>What about column names? With an indeterminate number of rooms, figuring out what to name the columns dynamically becomes an issue.
Exactly, the rooms in this hospital are about 20 per floor, but in others hospital could be more or
less.
So the columns must be generate dynamically always.

What do you mean "exactly"??? We ask you a question so we can help and all we get is "exactly"? Go back and read the questions I asked and read the link in my signature below for how to post data in a readily consumable format.

I'm getting real close to not wanting to help on this one because you won't answer the questions. 😉

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

steve dassin SSCrazy Points: 2396 More actions · Answer 3

Since you're getting flack you can try RAC 🙂

The RAC utility will let you do want you want very easily. For example:

create table Rooms (Room int primary key, State varchar(20), [Floor] varchar(10))

insert into Rooms

select 101 Room,'Occupied' State,'Floor 1' [Floor]

union all

select 102,'Occupied','Floor 1'

union all

select 103,'Occupied','Floor 1'

union all

select 104,'Occupied','Floor 1'

union all

select 105,'Cleaning','Floor 1'

union all

select 201,'Occupied','Floor 2'

union all

select 202,'Occupied','Floor 2'

union all

select 203,'Reserved','Floor 2'

union all

select 204,'Occupied','Floor 2'

union all

select 205,'Occupied','Floor 2'

union all

select 301,'Occupied','Floor 3'

union all

select 302,'Occupied','Floor 3'

union all

select 303,'Cleaning','Floor 3'

union all

select 304,'Occupied','Floor 3'

union all

select 305,'Out of Service','Floor 3'

union all

select 401,'Available','Floor 4'

union all

select 402,'Available','Floor 4'

union all

select 403,'Occupied','Floor 4'

union all

select 404,'Occupied','Floor 4'

union all

select 405,'Occupied','Floor 4'

union all

select 406,'Occupied','Floor 4'

Exec Rac

@transform='(Room) as Room',

@rows='[Floor]',

@pvtcol='Room',

@from='Rooms',

@defaults1='y',@rank='Room',@racheck='y',@shell='n'

Floor Room1 Room2 Room3 Room4 Room5 Room6

------- ----- ----- ----- ----- ----- -----

Floor 1 101 102 103 104 105

Floor 2 201 202 203 204 205

Floor 3 301 302 303 304 305

Floor 4 401 402 403 404 405 406

Here we're only displaying up to 4 rooms in any floor.

Exec Rac

@transform='Max(cast(Room as char(3))+~(~+State+~)~) as State',

@rows='[Floor]',

@pvtcol='Room', -- Rac treats Room dynamically.

@from='Rooms',

@defaults1='y',@rank='Room',@ranklimit=4,@racheck='y',@shell='n'

Floor Room1 Room2 Room3 Room4

------- ------------------- ------------------- ------------------- -------------------

Floor 1 101(Occupied) 102(Occupied) 103(Occupied) 104(Occupied)

Floor 2 201(Occupied) 202(Occupied) 203(Reserved) 204(Occupied)

Floor 3 301(Occupied) 302(Occupied) 303(Cleaning) 304(Occupied)

Floor 4 401(Available) 402(Available) 403(Occupied) 404(Occupied)

Obviously there's many ways to display (save) the data. Rac executes and saves data (to tables) on sql server. And Rac will never ask you why you are doing something. It's very obedient 🙂

For dynamic pivoting/crosstabs visit RAC @

www.rac4sql.net

best,

steve dassin

www.beyondsql.blogspot.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

rog pike (12/4/2008)
Exec Rac
@transform='(Room) as Room',
@rows='[Floor]',
@pvtcol='Room',
@from='Rooms',
@defaults1='y',@rank='Room',@racheck='y',@shell='n'
Floor Room1 Room2 Room3 Room4 Room5 Room6
------- ----- ----- ----- ----- ----- -----
Floor 1 101 102 103 104 105
Floor 2 201 202 203 204 205
Floor 3 301 302 303 304 305
Floor 4 401 402 403 404 405 406

I have to admit, that one's pretty clever. How long does it take to run and what's the limit on the number of columns?

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

fausto usme SSC Enthusiast Points: 105 More actions · Answer 5

>>Heh... No matter where it's done, I'll still like to know why the BA thinks >>that listing an unknown number hospital rooms horizontally is the right thing >>to do on any system

Hello Jeff,

I was thinking in horizontal rooms, because to the nurses is more easy to watch at the display (Monitor).

Right now i'm doing all code in the front end and is easy.

Again, Thanks a lot for everything.

fausto

steve dassin SSCrazy Points: 2396 More actions · Answer 6

Jeff Moden (12/4/2008)
rog pike (12/4/2008)
Exec Rac
@transform='(Room) as Room',
@rows='[Floor]',
@pvtcol='Room',
@from='Rooms',
@defaults1='y',@rank='Room',@racheck='y',@shell='n'
Floor Room1 Room2 Room3 Room4 Room5 Room6
------- ----- ----- ----- ----- ----- -----
Floor 1 101 102 103 104 105
Floor 2 201 202 203 204 205
Floor 3 301 302 303 304 305
Floor 4 401 402 403 404 405 406
I have to admit, that one's pretty clever. How long does it take to run and what's the limit on the number of columns?

Columns - for this execution probably around 450+ (that's conservative). Pivoting just an integer and eliminating the 'room' part of the string many more.

Performance - in general it depends and I'm not trying to be coy or evasive:) It's like asking how fast is sql server. There are parameters that act as optimizations that are obviously under user control. These parameters were meant for different types/sizes of tables being processed and output. Rac does not execute a 'query' for a solution. It builds a solution in discrete operations (the framework). By varing these operations (via parameters) the user can optimize (hopefully) the performance. Obviously there is no guarantee for every one of them. For example, for a small result table a simple sequential operation is all that's required at one stage. For a huge result a bulk operation with a user input batchsize should be used. So it depends:)

For this execute total execution in QA (include client stats) 187-235 ms.

Although the Rac famework will fit any (opps most) appropriate problems it's intended to solve there are obvious tradeoffs. When it comes to simple/trivial solutions the framework is overkill. A simple query would suffice. I had even thought about this. Since Rac has all the information about the nature of the solution why not bypass the famework for simple problem solving and construct a query. (And this is also a form of optimization). Why didn't I (and why don't I) do it? Because people taking advantage of Rac are not interested in trivial stuff. They are taxing it!:) Perhaps someone can take a different approach. If I had it to do over...well I have other ideas. But I have no intention of rewriting Rac. Once was quite enough 🙂

best,

steve

www.rac4sql.net

www.beyondsql.blogspot.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

fausto usme (12/5/2008)
>>Heh... No matter where it's done, I'll still like to know why the BA thinks >>that listing an unknown number hospital rooms horizontally is the right thing >>to do on any system
Hello Jeff,
I was thinking in horizontal rooms, because to the nurses is more easy to watch at the display (Monitor).
Right now i'm doing all code in the front end and is easy.
Again, Thanks a lot for everything.
fausto

Ah... understood. I think you're probably doing it the right way... in the GUI. If it were aggregated data, I'd say do it on the server to be "kind to the pipe". But, since there's no aggregation, doing the pivot in the GUI is the right thing to do because it saves clock cycles on the server.

Thanks for the feedback.

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