convert a roow of data under a single column heading

Question

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

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

SELECT [Number 1] AS [Number] FROM [Number} WHERE [ID] = 1

UNION ALL

SELECT [Number 2] AS [Number] FROM [Number} WHERE [ID] = 1

UNION ALL

SELECT [Number 3] AS [Number] FROM [Number} WHERE [ID] = 1

Or if there a lot of columns you could do a loop and use dynamic sql

Far away is close at hand in the images of elsewhere.
Anon.

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 2

Thanks David!

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 3

yes we can do a loop.

But if My number table is #Number table 'a # table in stored procedure'

Then how can i find the loop termination value.i.e till how many times i have to execute the loop.

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

David Burrows SSC Guru Points: 65142 More actions · Answer 4

OK assuming by your first post that

1. You do not know how many columns are in the table

2. The numbers in the column names are not consecutive

3. ID is always the first column

4. All the other columns are named [Number n]

SELECT COUNT(*) FROM tempdb.dbo.syscolumns WHERE [id] = object_id('tempdb.dbo.#Number')

will give you the number of columns in the table

loop using @colid from 2 to count above

SET sql = 'SELECT [' + [name] + '] AS [Number] FROM [#Number} WHERE [ID] = 1'

FROM tempdb.dbo.syscolumns

WHERE [id] = object_id('tempdb.dbo.#Number')

AND colid = @colid

execute the sql

Far away is close at hand in the images of elsewhere.
Anon.

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 5

Thanks David this was really a nice reply.!It worked

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration