December 10, 2008 at 2:58 pm
I need to have my column names as
source,type ,10/11,09/11,08/11,07/11,06/11,05/11
how can i get that ..when i execute my sp i am getting the output as above format but i want that output to be into a table .......with the same column names how can i do that ...
i have another question is ther any option to rename a column name to getdate() or something like that
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 3:10 pm
Hi,
The column names shouldn't be a problem, so long as you put them in square brackets.
E.g.
create table #temp
(
[source] varchar ,
[type] int,
[10/11] int,
[09/11] int,
[08/11] int,
[07/11] int,
[06/11] int,
[05/11] int,
[getdate()] datetime
)
select * from #temp
If you have a query that you want to use to build your table you can use
"SELECT .... INTO mytable FROM source".
This will create the table with the column names
E.g.
select getdate() as [getdate()] into #temp2
select * from #temp2
B
December 10, 2008 at 3:17 pm
create table #temp
(
[source] varchar ,
[type] int,
[10/11] int,
[09/11] int,
[08/11] int,
[07/11] int,
[06/11] int,
[05/11] int,
[getdate()] datetime
)
this column names will not be a problem but the problem here is the date 10/11,09/11 will be changing day by day ...it starts with getdate() and should end by getdate()-7 so i need to have 8 dates as my column names everyday..so it vary everyday so is there any way to do that
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 3:26 pm
what exactly i need is
today the output of the sp would be
column names
source,type,10/12,09/12,08/12,07/12,06/12,05/12
tommorow it would be
column names
source,type,11/12,10/12,09/12,08/12,07/12,06/12
after which i move this output data into a table in whihc the column names should be the same way as above which i am trying to do ...please help me out ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 3:33 pm
hmmmmm. I did something a while back that may be similar to your requirements. I ended up using a dynamic sql string and a temp table inside a stored proc to get the results.
What I was trying to show was branch compliance for the last 8 days...
STEP ONE: Build dynamic query string (replace @startdate for getdate() if you want)
declare @myQuery varchar(1024)
set @myQuery = 'select
, [name_branch] + '' ('' + cast([id_branch_supp] as varchar(4)) + '' )'' as branch, ['+
cast(day(@startDate-7) as varchar(2)) +'],['+ cast(day(@startDate-6) as varchar(2)) +'],['+
cast(day(@startDate-5) as varchar(2)) +'],['+ cast(day(@startDate-4) as varchar(2)) +'],['+
cast(day(@startDate-3) as varchar(2)) +'],['+ cast(day(@startDate-2) as varchar(2)) +'],['+
cast(day(@startDate-1) as varchar(2)) +'],['+ cast(day(@startDate) as varchar(2)) + '] from #TempTableWithReportData';
STEP TWO: create report data and place in temp table #TempTableWithReportData. I used a pivot table which pivoted based on day_invoiced.
SELECT blah, blah, blah INTO #TempTableWithReportData
STEP THREE: run the dynamic SQL
exec (@myQuery )
I know this isn't exactly what you are trying to do but it might point you in the right direction. I think you will need to use some dynamic SQL to get what you want.....?
B
December 10, 2008 at 3:41 pm
Thanks for ur Support
source,type,10/12,09/12,08/12,07/12,06/12,05/12
but the above output which iam getting is from dynamic sql
when i execute this --exec @strssql --i am getting that output after which i wnat to move to table which i couldnt do that for which i have been trying
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 3:46 pm
So... as each day comes along you want to add a NEW column to the final destination table? i.e. after a year there would be 365 columns in the table?
or would you prefer the final table to only have x amount of columns?
December 10, 2008 at 3:51 pm
no i am not adding an extra column its like startdate = getdate()
and enddate= getdate()-7
so itt will reduce a date and add a new date
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 3:57 pm
When you run your stored proc do you get the full output of seven days? If so you could drop the table at the start of the sp and recreate the table at the end of the sp , by using SELECT INTO, with your final select statement.
December 10, 2008 at 4:05 pm
yes i am getting that but how should i do create the table and dr0p it i dont know that
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 4:08 pm
Can you post your stored proc and then I can show you the changes.
December 10, 2008 at 4:20 pm
its a bit confidential so can i have your mail id so that i shall mail it to u ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
December 10, 2008 at 6:10 pm
Click on my name to the left and there will be an option to send email...
December 11, 2008 at 4:02 pm
Chris (12/10/2008)
what exactly i need istoday the output of the sp would be
column names
source,type,10/12,09/12,08/12,07/12,06/12,05/12
tommorow it would be
column names
source,type,11/12,10/12,09/12,08/12,07/12,06/12
after which i move this output data into a table in whihc the column names should be the same way as above which i am trying to do ...please help me out ....
Apparently, this is the same story, you posted in
SMALL HELP IN PIVOTING THE DATA
I hope you have got the answer now (without having to post your confidential code :))
Chris (12/10/2008)
its a bit confidential so can i have your mail id so that i shall mail it to u ...
We request you avoid cross posting and be clear in your requirements in future to avoid confusion.
Before posting a question please read:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply