Hi,

I've been looking at the documentation, but thougth I'd ask you to maybe save time.

Say I have a temp table with the following columns
create table #person (
person_id int,
last_name varchar(10),
first_name varchar(10),
ssn varchar(11))

say I stuffed a bunch of data in it
1 Schuele Amy 12345678901
2 Quinn Steve 23456789012

what I want as my result set is: (the order is not important)
1, last_name, Schuele
1, first_name, Amy
1, ssn, 12345678901
2, last_name, Quinn
2, first_name, Steve
2, ssn, 23456789012

so, I want the column name as part of the result set, returned as a string.

Obviously I'm trying to get the data out into (id) name:value pairs for some
processing down the line.

Thanks for any ideas!
Amy

I don't know what you mean by "the order is not important". Does it mean
that the output order could be random? A simple union should do the trick:

select person_id, 'last_name', last_name
from #person
union
select person_id, 'first_name', first_name
from #person
union
select person_id, 'ssn', ssn
from #person
order by person_id

"Amy" <aschuele (AT) ncsa (DOT) uiuc.edu> wrote

Thanks for the suggestion.

What I should have pointed out is that this is a small example. My real
table has many more columns, including address, phone, etc. So, while
this will work it would be cumbersome. I was hoping to find a solution
that doesn't require as many lines of code.

Thanks again!
Amy

"Carl Kayser" <kayser_c (AT) bls (DOT) gov> wrote

aschuele (AT) ncsa (DOT) uiuc.edu (Amy) wrote in message news:<6d1ab15.0311061333.71b63118 (AT) posting (DOT) google.com>...

As long as you don't care about the order of the results, you could
try something like the following:

select person_id, "last_name", last_name from #person
select person_id, "first_name", first_name from #person
select person_id, "ssn", ssn from #person
go

The only drawback is the need to manually specify each column you want
with a seperate select line. It's possible to retrieve column names
using the sysobjects and syscolumns tables, but to use them to do what
you want, you'd probably need to use a cursor, and even then I'm not
aware of being able to do selects by passing dynamic column names into
a single select statement.

Dear Amy

Just try it out

select person_id,",last_name,",last_name+char10)
,person_id,",last_name,",last_name+char(10)
, person_id,",ssn",ssn
from #person

I hope your problem must be solved.

Navneet

"Amy Schuele" <aschuele (AT) ANTISPAM (DOT) ncsa.uiuc.edu> wrote