Friday, March 30, 2012

Output Column names in each Row along with the row Value

Hello,

I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3

Thank you! :-)

YasOn Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?
>
eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?
>
Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3
>
Thank you! :-)
>
Yas


Not sure what you are actually looking at

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')

select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp

drop table #temp|||On 29 Jun, 14:27, M A Srinivas <masri...@.gmail.comwrote:

Quote:

Originally Posted by

On Jun 29, 3:09 pm, Yas <yas...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hello,


>

Quote:

Originally Posted by

I was wondering if anyone can help me figure something out.
Is it possible to do a querey in MS SQL server and have the results
returned so that each result in each row is preceeded by the column
name?


>

Quote:

Originally Posted by

eg. instead of usual output -colName1, colValue1,
colName2,colValue2,colName3,colValue3 ?


>

Quote:

Originally Posted by

Also I would like to only have this for certain columns ie in the
above example only for columns 2 and 3


>

Quote:

Originally Posted by

Thank you! :-)


>

Quote:

Originally Posted by

Yas


>
Not sure what you are actually looking at
>


Hi, thanks for the response. Basically I want to have a query in a DTS
package that outputs to a text file with each line containing 1 row of
results.
What I would like to have is on each line before a given value is
written the column header for that value is also written. the file is
a semicolon delimmeted file so it would look something like this...

col1Name;value1;col2Name;value2;col3Name;value3... .etc

so...
FirstName;firstNameValue;SecondName;secondNameValu e;Occupation;occupationValue..etc

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?
especially as I want to do this in a DTS package Export Data which
esentially has a create table type command to ouptut to a text file
and throws errors that destination column name not defined.

Thanks :-)

Quote:

Originally Posted by

create table #temp (col1 int, col2 int , col3 varchar(10))
insert into #temp values (1,10,'abcd')
insert into #temp values (2,20,'efgh')
>
select col1, ' col2:'+cast(col2 as varchar(10))+ '-col3:'+col3 as
col23
from #temp
>
drop table #temp

|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?


That would indeed be the way to do it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 29 Jun, 23:38, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

Now I can just do...
SELECT 'FirstName', firstName, 'SecondName', secondName,
'Occupation' occupation
FROM Table1
Which would output the table I want with the values between '' showing
up in unnamed columns... BUT is there a better way of doing this?


>
That would indeed be the way to do it.


Thanks! :-)

No comments:

Post a Comment