I am wondering if there is a way to write the SQL so that it would return me a result as usual but now, on the first row that would return also the attribute names.
To explain what I mean:
say you have a table "test" which has 2 attributes "id" and "name":
id name
1 nik
2 tst
query:
SELECT * FROM test;
produces:
1 nik
2 tst
but what I want it to return is this:
id name
1 nik
2 tst
Is this possible?
edit: I am using PostreSQL
You cannot return the names and the actual column values in a single result unless you give up on the real datatypes (which is probably not what you want).
Your example mixes character data and numeric data in the id column and Postgres will (rightfully) refuse to return such a result set.
Edit:
I tested the "union" solution given e.g. by JNK and it fails (as expected) on Postgres, Oracle and SQL Server precisely because of the non-matching datatypes. MySQL follows it's usual habits of not throwing errors and simply converts everything to characters.
Extremely generic answer since you don't provide an RDBMS:
SELECT id, name FROM(
SELECT 'id' as 'id', 'name' as 'name', 1 as 'Rank'
UNION ALL
SELECT *, 2 as 'Rank' FROM test) as X
ORDER BY [RANK]
EDIT
Thanks to Martin for pointing out the need for the ORDER BY
Assuming you are on SQL Server, you can get the column names of a specific table by using this query:
select column_name 'Column Name', data_type 'Data Type'
from information_schema.columns
where table_name = 'putYourTableNameHere'
Then, you'll have to UNION your things together.
information_schema to fetch column names is interesting idea, however note that above query actually returns multiple rows, so you also need to additionally construct pivot table (that is, with multiple columns) for UNION.
Depending on your tools / technique, but here are a couple:
If you're using SSMS (Sql-Server), and want to copy/paste your results with the column headers:
If you're using Sql-Server, you can query meta-tables (sys.columns, etc.)
If you're using an ASP.NET databound control, you usually have access to methods or properties (sqldatareader.getname(i), etc.)
Anyway -- just depends on the layer you're trying to get the names from -- if these above don't help, then edit / re-tag your question so we can focus on whatever tool you're wanting to use to do this.
EDIT for PostgresSQL
I agree with OMG Ponies above, the way to get this meta-data is usually from the interface you use.
for example the Perl DBI module has a method fetchrow_hashref where the columns of the returned row are returned as an associative array (hash) where the colnames are the keys.
print $ref->{'name'}; # would print nik or tst
Update: I had forgotten to add that some of these interface layers have a method that return s the col names and you could use that instead of adding the names into your result set. The DBI method you'd use would be $sth->{NAMES}->[0] would return the first column name.
