Show results in columns vs rows
I need to have my results displayed in as many columns as the results need (the number of columns will vary depending on the record) instead of the rows the results are currently being written to.
Here's a sample resultset as it is dumped into rows:
ID lname fname response current
100 Doe John Mickey Donald
100 Doe John Mickey Tweety
100 Doe John Mickey Daffy
100 Doe John Minnie Pluto
100 Doe John Daffy Pluto
Here's what I need it to be:
ID lname fname response1 response 2 current1 current2 current3
100 Doe John Mickey Donald Tweety Daffy
100 Doe John Minnie Daffy Pluto
Or it can be like this: (This way would be awesome!)
ID lname fname response1 current1 response2 current2
100 Doe John Mickey Donald Minnie Pluto
Tweety Daffy
Daffy
To complicate matters, the database this is being written against is Intersystems' Caché, which is FAR from SQL Server unfortunately, so I can't do temp tables, stored procs or other normal database stuff.
Here's a sample resultset as it is dumped into rows:
ID lname fname response current
100 Doe John Mickey Donald
100 Doe John Mickey Tweety
100 Doe John Mickey Daffy
100 Doe John Minnie Pluto
100 Doe John Daffy Pluto
Here's what I need it to be:
ID lname fname response1 response 2 current1 current2 current3
100 Doe John Mickey Donald Tweety Daffy
100 Doe John Minnie Daffy Pluto
Or it can be like this: (This way would be awesome!)
ID lname fname response1 current1 response2 current2
100 Doe John Mickey Donald Minnie Pluto
Tweety Daffy
Daffy
To complicate matters, the database this is being written against is Intersystems' Caché, which is FAR from SQL Server unfortunately, so I can't do temp tables, stored procs or other normal database stuff.
ASKER
Thank you Icohan, but unfortunately Intersystems' Caché doesn't support pivot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, what complicates this more is we inherited Caché when it came with a vended product. That vendor doesn't allow us to do pretty much anything with Caché. The vendor has blocked the areas in the System Management Portal that enable DeepSee.
Looks like I'm probably up the creek on this. Sounds like I need to start building macros into a spreadsheet that can manipulate the data in its current form to make it what I need.
Gotta love these situations...
Thank you Icohan. I appreciate your diligent help.
Looks like I'm probably up the creek on this. Sounds like I need to start building macros into a spreadsheet that can manipulate the data in its current form to make it what I need.
Gotta love these situations...
Thank you Icohan. I appreciate your diligent help.
"Sounds like I need to start building macros into a spreadsheet that can manipulate the data in its current form to make it what I need."
Can't you bring it in mySQL instead or any other database - even SQL Express if is not > 2GB and do it there instead? Maybe that's easier than Excel.
Also, "hat vendor doesn't allow us to do pretty much anything with Caché. The vendor has blocked the areas in the System Management Portal that enable DeepSee." - I would check if is not to dificult to see if this was done on purpose due to some licensing because if it does not require additional money then..you get the idea - is your right to use the product you paid for to FULL extent instead of bending backwards to reinvent something thats built in.
If is money related see maybe you can build a case to get it "unlocked" and utilized elsewhere as BI...must be useful to someone over there if they have the money to buy such product.
Good luck!
Can't you bring it in mySQL instead or any other database - even SQL Express if is not > 2GB and do it there instead? Maybe that's easier than Excel.
Also, "hat vendor doesn't allow us to do pretty much anything with Caché. The vendor has blocked the areas in the System Management Portal that enable DeepSee." - I would check if is not to dificult to see if this was done on purpose due to some licensing because if it does not require additional money then..you get the idea - is your right to use the product you paid for to FULL extent instead of bending backwards to reinvent something thats built in.
If is money related see maybe you can build a case to get it "unlocked" and utilized elsewhere as BI...must be useful to someone over there if they have the money to buy such product.
Good luck!
http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Worst case if none of us here can help I see "David E. Nelson" has some training session on "Creating DeepSee Models, Pivot Tables, Dashboards, and KPIs"