December 3, 2008 at 10:22 am
Hi everyone,
How can I add mulitple columns together in SQL 2005 and those columns are containing Dates and times But I wish to add only Dates together.
For Example:
A B c D
1/1/2008 1/3/2008 1/1/2008 1/4/2008
wish to display a result like this:
E
1/1/2008
1/3/2008
1/1/2008
1/4/2008
Thank You Very Much For your answers
Jo
December 3, 2008 at 10:53 am
You want to use UNPIVOT for this,
If you post your exact table structures and some sample data, then I can provide some code that should work
December 3, 2008 at 11:02 am
... and to strip off the time from a datetime column, you can use CONVERT
declare @sample datetime
set @sample = getdate()
select @sample as DateAndTime,CONVERT(char(10),@sample,101) as DateOnly
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 3, 2008 at 11:40 am
A B C D
1/1/2008 1/3/2008 1/1/2008 1/4/2008
1/5/2008 1/7/2008 1/9/2008 1/11/2008
wish to display a result like this:
Assign four columns into one column and that column Name E
E
1/1/2008
1/5/2008
1/3/2008
1/7/2008
1/1/2008
1/9/2008
1/4/2008
1/11/2008
Thank You Very Much
December 3, 2008 at 11:43 am
Hi Bob,
Thank You Very Much for your kindness of showing me how to write a script in SQL. May I ask you one stupid question? Since I am new to SQL, if I ask you a stupid question please don't laugh or mad at me.
Is the script that you've provided to me and that scripts will provide as the result showing under. If not, would you please help me?
A B C D
1/1/2008 1/3/2008 1/1/2008 1/4/2008
1/5/2008 1/7/2008 1/9/2008 1/11/2008
wish to display a result like this:
Assign four columns into one column and that column Name E
E
1/1/2008
1/5/2008
1/3/2008
1/7/2008
1/1/2008
1/9/2008
1/4/2008
1/11/2008
Thank You Very Much
Joe
December 3, 2008 at 1:53 pm
I'm not laughing or mad, Joe. But you actually had two questions. Steveb already gave you a link to the UNPIVOT operation, which has an example of how to combine multiple columns into a single output column. I was just answering the half of your question about dropping the time from the date. You should at least look at the links you are referred to and try to work with the examples already given there. You should also search the forums for other questions similar to yours, as I know there are one or more threads already in here that address either of your questions. Many of us are at work and may not always have time to work up a query for you. 😉
That said, here is an example of the UNPIVOT operation at work for the situation you described. I used a CTE to do the unpivot and then waited until the final select query to convert it from a datetime format to a char(10) format. If you convert to char(10) prior to displaying the data, your ORDER BY will not sort the 12/04/2007 date correctly.
Good luck.
---------------------------------------------------------------------------------------------------------
Declare @pivotTable table (id int identity(1,1) primary key,date1 datetime,date2 datetime,date3 datetime,date4 datetime)
INSERT INTO @pivotTable
select getdate(),getdate()-365,getdate()+2,getdate()+3
union allselect getdate()+3,getdate()+4,getdate()+7,getdate()+2
union allselect getdate()+5,getdate()+4,getdate()+1,getdate()+2
select * from @pivotTable
--Unpivot the table with a CTE
;with unpivotCTE as
(SELECT ID,[Date]
FROM
(SELECT ID, Date1, Date2, Date3, Date4
FROM @pivotTable) p
UNPIVOT
([Date] FOR DateCol IN
(Date1, Date2, Date3, Date4)
)AS unpvt
)
select convert(char(10),[Date],101)
from unpivotCTE
order by [Date]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 3, 2008 at 2:03 pm
Bob:
Read this the other day on Gail's blog, think you may find it interesting. I used to do this the same way you do. Not anymore!
http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/
Thanks Gail.
December 3, 2008 at 2:10 pm
Hi Bob,
Thank You Very Much for your helps, I'm really appreciated your wonderful helps.
God Bless you Bob,
Thanks
Joe
P.S: I did not see any links that SteveB sent it to me. If you do see that link, would you please sent it to me.
Again, Thanks so much for your helps.
December 3, 2008 at 2:21 pm
Hi Steve,
How are you doing? Would you please help me by answering my question? I know you are very busy, but if you have time. Please help me and sent me a link of UNPIVOT operation, which has an example of how to combine multiple columns into a single output column.
Thank You Very Much SteveB,
God Bless you
Joe
December 3, 2008 at 2:45 pm
Joe,
My error and my apologies. I thought there was a link. I must have been hallucinating.
Here is the link in MS-Help.
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/24ba54fc-98f7-4d35-8881-b5158aac1d66.htm
Seth,
Thanks for the link. I've seen the datediff technique but never saw any comparison stats before. When I first needed to drop time, the first solution I ever came up with was cast(left(dateCol,11) as datetime) :w00t: Herd instinct drove me to CONVERT because that's what everyone else seemed to be doing.
God bless us, every one.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 3, 2008 at 2:54 pm
Bob Hovious (12/3/2008)
When I first needed to drop time, the first solution I ever came up with was cast(left(dateCol,11) as datetime) :w00t:
Using LEFT! UGH! THE HORROR! THE AGONY!
...
Yeah, I definitely did that at first too :hehe:
December 4, 2008 at 6:52 am
Hi Bob,
somehow when I copied and pasted it then it won't work. If you don't mind, would you please copy the link for me? So I can click on it then it will direct me to that site.
Thanks Bob,
Joe
December 4, 2008 at 6:56 am
Joe,
The link wasn't intended to be used on the internet, it's a link for MS Help. If you open up your books online (Hit F1 from SSMS or Query Analyzer), click the GO menu option and select URL, you can put Bob's link in.
December 4, 2008 at 6:58 am
If you can't get that to work for you, here is a link to the equivalent online page:
December 4, 2008 at 7:04 am
Hi Bob,
I'm really sorry to bother you too much. I have a question that relates to your script, so I can understand it.
The script provided below is the one you sent it to me
What if I have a table which has multiple columns and those columns contain the dates and I wish to merge those dates columns into one new column? Do I still have to use pivot table?
Why you have to be declared the pivottable? Because you wish to use pivottable right? and my next question is why getdate()-365, getdate()+2, getdate()+3 ? why you substract 365 (one year), add 2, add 3? then union all select getdate()+3, getdate()+4, getdate()+7, getdate()+2? why you add 3, 4, 7, and 2? and add 5, 4, 1, and 2?
would you please explain them for me?
THank You Very Much
Joe
Declare @pivotTable table (id int identity(1,1) primary key,date1 datetime,date2 datetime,date3 datetime,date4 datetime)
INSERT INTO @pivotTable
select getdate(),getdate()-365,getdate()+2,getdate()+3
union all select getdate()+3,getdate()+4,getdate()+7,getdate()+2
union all select getdate()+5,getdate()+4,getdate()+1,getdate()+2
select * from @pivotTable
--Unpivot the table with a CTE
;with unpivotCTE as
(SELECT ID,[Date]
FROM
(SELECT ID, Date1, Date2, Date3, Date4
FROM @pivotTable) p
UNPIVOT
([Date] FOR DateCol IN
(Date1, Date2, Date3, Date4)
)AS unpvt
)
select convert(char(10),[Date],101)
from unpivotCTE
order by [Date]
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply