adding Column

Question

Post reply

adding Column

cpinky01

Ten Centuries

Points: 1144
More actions
September 10, 2010 at 8:55 am

#229212

Hi,
I have one column in my table with name PeriodDescription which is of varchar type.
The data in the column is of the following format.
Q1 2009
Jul – Sep
Q2 2009
Oct – Dec
Q3 2009
Jan - Mar
Q4 2009
Apr - Jun
Q1 2009
Jul - Sep
Q2 2009
Oct – Dec
Actually I need to have one more column that has to represent PeriodDescription with our
that is like this.
Q1 2009 Jul – Sep
Q2 2009 Oct – Dec
Q3 2009 Jan - Mar
Q4 2009 Apr - Jun
Q1 2009 Jul - Sep
Q2 2009 Oct – Dec
Can anyone please help me to do this.
Thank you

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please edit the following code so that the values exactly match your table:

IF OBJECT_ID( 'tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable

CREATE TABLE #MyTable (PeriodDescription VARCHAR(20))

INSERT INTO #MyTable (PeriodDescription)

SELECT 'Q1 2009' UNION ALL

SELECT 'Jul – Sep' UNION ALL

SELECT 'Q2 2009' UNION ALL

SELECT 'Oct – Dec' UNION ALL

SELECT 'Q3 2009' UNION ALL

SELECT 'Jan - Mar' UNION ALL

SELECT 'Q4 2009' UNION ALL

SELECT 'Apr - Jun' UNION ALL

SELECT 'Q1 2009' UNION ALL

SELECT 'Jul - Sep' UNION ALL

SELECT 'Q2 2009' UNION ALL

SELECT 'Oct – Dec'

SELECT * FROM #MyTable

This will help folks understand a) what you've got and b) what to do next.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

steveb. SSC-Forever Points: 46733 More actions · Answer 2

Do you have a primary key ? if not then it is not a table is is called a heap and makes the query a lot more complex and un-reliable

cpinky01 Ten Centuries Points: 1144 More actions · Answer 3

Thank you for your reply.

I got waht i wanted by using substring.

Thanks once agin for your reply.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

cpinky01 (9/10/2010)
Thank you for your reply.
I got waht i wanted by using substring.
Thanks once agin for your reply.

@steve-2: I don't know about you but I'm totally lost.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

steveb. SSC-Forever Points: 46733 More actions · Answer 5

As long as he got what he wants then i am happy (and confused)

I would like to see the code for the solution though and it may be useful for others if it can be posted.