column on first row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

column on first row

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-03 : 10:52:15

i have done this:

select name,age,gender,status,2 as ordering
into #A
from tableA
UNION ALL
select 'name','age','gender','status',1 as ordering
order by ordering asc

select name+'|'+age+'|'+gender+'|'+status [output]
into #B
from #A

select output from #B

i would like the output shows the column name first instead of the result. already add the ordering.

but the result doesnt show the column at the first row.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-03 : 10:58:19

select name,age,gender,status,2 as ordering
into #A
from tableA
UNION ALL
select 'name','age','gender','status',1 as ordering

select name+'|'+age+'|'+gender+'|'+status [output]
--into #B
from #A
order by ordering asc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-03 : 11:03:05

You have to add the ordering clause to your final query. When you save the data into a table (in this case table #A and subsequently table #B), the data is stored with no consideration to ordering. Data in a SQL table is an unordered collection. The ONLY way to guarantee ordering in the output is to add the order by clause into your final select.

select name+'|'+age+'|'+gender+'|'+status [output],ordering
into #B
from #A

select output from #B order by ordering;

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-03 : 11:04:46

but i need to filter those not null:

.....

select output from #B
where output is not null

here is where the ordering not in order

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-03 : 11:27:51

Then you should add the order by clause in that select.

select output from #B
where output is not null
order by ordering

You always HAVE to indicate the ordering in the final select. Doing it earlier in the process would not help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources