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@ncsa.uiuc.edu> wrote in message
news:6d1ab15.0311061333.71b63118@posting.google.co m...
> 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

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@bls.gov> wrote in message
news:bog1pr$j21$1@blsnews.bls.gov...
> 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@ncsa.uiuc.edu> wrote in message
> news:6d1ab15.0311061333.71b63118@posting.google.co m...
> > 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
>
>

aschuele@ncsa.uiuc.edu (Amy) wrote in message news:<6d1ab15.0311061333.71b63118@posting.google.c om>...
> 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


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@ANTISPAM.ncsa.uiuc.edu> wrote in message news:<muOqb.137933$Fm2.114575@attbi_s04>...
> 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@bls.gov> wrote in message
> news:bog1pr$j21$1@blsnews.bls.gov...
> > 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@ncsa.uiuc.edu> wrote in message
> > news:6d1ab15.0311061333.71b63118@posting.google.co m...
> > > 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
> >
> >