pivot table

Question

Post reply

pivot table

lavanya.pothu

SSC Enthusiast

Points: 101
More actions
May 5, 2010 at 12:55 am

#232371

Please help
Pivoting table should show all days in month as columns with respective data.....

Viewing 7 posts - 1 through 6 (of 6 total)

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

I would be glad to help, but I need some more information.

Take a look at the article linked in my signature, you'll find out how to ask for help effectively.

We basically need:

1) Table scripts

2) Sample data

3) Desired output

4) What you have tried so far

-- Gianluca Sartori

lavanya.pothu SSC Enthusiast Points: 101 More actions · Answer 2

i have table

roomNo,blockFrom,blockTo

blockFrom and blockTo are date fields.....

i insert data like

501 1-Mar-2010 5-Mar-2010

502 1-Mar-2010 5-Mar-2010

503 1-Mar-2010 5-Mar-2010

504 1-Mar-2010 5-Mar-2010

501 10-Mar-2010 15-Mar-2010

etc...

i want to display them as

room No 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 total days in month

501 R R .. R R R R R R

502 R R ..

503 R R

504 R R

R stands for reserved these data will get from table

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

Dear Lavanya, you have not even provided one of the requisites that Gianluca has asked for! Please provide us those and then we will provide you with awesome code! Guessing and providing a lame-code is not worth the time and effort we and you had put for!!

si_downes SSC Eights! Points: 890 More actions · Answer 4

so if there isn't a reservation on that date the date isn't displayed across the top of the table as a column? I'm currently looking for a solution to the other side of this issue, I don't want to display the column if there isn't a record.

If I've got you right then your solution is to form your query joined on a date dimension table, so the dataset will always return that date even if there is no result on that day. Well that works and is how I've got myself in the problem of not being able to get rid of empty dates.

Is there a 'suppress' function is SSRS to remove empty rows/columns from a matrix? I'm going to open a new thread for my problem

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

lavanya.pothu (5/5/2010)
i have table
roomNo,blockFrom,blockTo
blockFrom and blockTo are date fields.....
i insert data like
501 1-Mar-2010 5-Mar-2010
502 1-Mar-2010 5-Mar-2010
503 1-Mar-2010 5-Mar-2010
504 1-Mar-2010 5-Mar-2010
501 10-Mar-2010 15-Mar-2010
etc...
i want to display them as
room No 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 total days in month
501 R R .. R R R R R R
502 R R ..
503 R R
504 R R
R stands for reserved these data will get from table

Hi lavanya,

Your kind of new to this forum so let me help you a bit. Please read the following article so people can actually help you. If you post the data and table structure in the manner the article teaches, people may actually fight over trying to help you faster than anyone else... and you'll get what you really seek, as well... a reply that contains tested code that actually works.

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

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

ColdCoffee (5/5/2010)
Dear Lavanya, you have not even provided one of the requisites that Gianluca has asked for! Please provide us those and then we will provide you with awesome code! Guessing and providing a lame-code is not worth the time and effort we and you had put for!!

Heh... calm down and stop using prov words like "Lame". The guy is just a newbie and doesn't know. And you didn't post the "Forum Etiquette" article for the newbie to follow like Gianluca did so how is the newbie supposed to know exactly what's needed? Back Gianluca up by reposting the link.

And, if I recall... you said you were going to put the link to the "Forum Etiquette" article in your signature line for easy referral... I see you haven't done that yet. 😉

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