Get Dates of Month listed as Columns

Question

Post reply

Get Dates of Month listed as Columns

vani_r14

SSCrazy

Points: 2843
More actions
November 26, 2011 at 4:29 pm

#390958

Hi all
I am trying to come up with a Script that lists the dates individually in each month in a column format.
Currently i have written a function that returns a table and returns the dates in the given month in rows. Can someone please suggest an idea for that to appear in a column.
E.g. All days in the month of November. Currently the function returns it as -
27/11/2011
28/11/2011
29/11/2011
30/11/2011
What i need to is return it as
27/11/2011 28/11/2011 29/11/2011 30/11/2011
Any help is appreciated
thanks
vani

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

The only thing I can suggest is, have you looked at the use of the PIVOT statement? I would suggest reading:

http://qa.sqlservercentral.com/scripts/T-SQL/70819/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 2

I'd rather go with the classic CrossTab approach (see related links in my signature).

The code is not only easier to read and to expand (e.g. to include "nested" pivots), it's also supposed to be faster than the PIVOT solution.

For your scenario you might want to go one step further and use the DynamicCrossTab method with month and year as input parameter.

However, the most elegant solution would be using a frontend (e.g. Reporting Services) to pivot the data since it's usually a presentation requirement. Hence it should be done at the presentation layer. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

vani_r14 (11/26/2011)
Hi all
I am trying to come up with a Script that lists the dates individually in each month in a column format.
Currently i have written a function that returns a table and returns the dates in the given month in rows. Can someone please suggest an idea for that to appear in a column.
E.g. All days in the month of November. Currently the function returns it as -
27/11/2011
28/11/2011
29/11/2011
30/11/2011
What i need to is return it as
27/11/2011 28/11/2011 29/11/2011 30/11/2011
Any help is appreciated
thanks
vani

First, to show what a "Pivot" and "Cross Tab" is and why some folks (especially me) thinl that "Cross Tab" is better for such things (including some performance testing), please see the following article.

http://qa.sqlservercentral.com/articles/T-SQL/63681/

For how to do it dynamically so that you don't need to make code changes every month, please the the following "follow up" article.

http://qa.sqlservercentral.com/articles/Crosstab/65048/

C'mon back for additional information if you still have problems after reading those two articles (teach a person to fish).

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

vani_r14 SSCrazy Points: 2843 More actions · Answer 4

Hi Jeff

Thank you so much for your help and the post.

I am half way through it now 🙂

Vani

vani_r14 SSCrazy Points: 2843 More actions · Answer 5

Hi all

Thank you for your help Jeff. I have got that working now.

I have two tables now (one with client details) and one with the dates. There is nothing in common between these two tables and i need to come up with a SS that has all client details and the dates listed as heading. any suggestions

e.g.

Client details 01/11/2011 02/11/2011 03/11/2011 04/11/2011 .... 30/11/2011

x some value some value some value some value some value

y some value some value some value some value some value

z some value some value some value some value some value

thanks again

Vani

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

vani_r14 (11/28/2011)
There is nothing in common between these two tables

The Client Details table MUST have something in it that can be referred to by date or can be joined to a different table that has some form of date or your task is simply not possible.

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

vani_r14 SSCrazy Points: 2843 More actions · Answer 7

Hi Jeff

Thank you for your help :). I got it working.

I created a table with the client details, joined it with the temp table with the dates using an id

Thanks

Vani