displaying the values of a column as rows using looping statement in sql server

Question

0.00/5 (No votes)

See more:

Hello Everyone,

SQL

Select NewDate INTO #MyTable from @Months
--while(NewDate<12)
select convert( varchar(50), NewDate, 103 ) as 'NewDate' from #MyTable
drop table #MyTable

As in the above, I have a temporary table from which I am getting the the dates(values) for a column called 'NewDate'. As:

output:

VB

NewDate
07/01/2011
07/02/2011
07/03/2011
07/04/2011
07/05/2011

Here I need to loop through all the values of NewDate in order to display them in a column format.. As:

VB

NewDate  07/01/2011 07/02/2011 07/03/2011 07/04/2011 07/05/2011

Please any one suggest me, how to use the looping and get the rows(values) as columns. I don't want to use PIVOT.

Regards,

Posted 8-Mar-11 17:36pm

Raj.rcr

Updated 8-Mar-11 20:39pm

v5

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2011-03-08T17:42:00

Solution 1

Some solutions here[^] might give you some ideas.

Posted 8-Mar-11 17:42pm

Abhinav S

Comments

Raj.rcr 8-Mar-11 23:52pm

No, I want to use the loop to get display result in the format I asked.. I have gone through the link, there is no looping condition at all..

Albin Abel · Answer 2 · 2011-03-08T18:27:00

Solution 2

Generally column to rows or Rows to colums, use PIVOT method. You can include format on the query or after pivoting.

It may be easier and better to do formatting after pivoting, because pivoting itself is a costly operation and doesn't need to put more load on it.

Here is the Microsoft documentation of pivoting method

http://msdn.microsoft.com/en-us/library/ms177410.aspx[^]

Posted 8-Mar-11 18:27pm

Albin Abel

Comments

Raj.rcr 9-Mar-11 1:03am

I am given the task not to use PIVOT... Can u plz give me an idea using the loop, where we can use alter table and exec sp?