Select statement data formatting 1

[jecairns]

Hi,

I am looking for some suggestions to change my data, in the format that it is in (below):

p0 p1 p2
------------ ------------ ------------
100 1000 1
100 1000 3
200 2000 2021
200 2000 4
200 2000 5
200 2001 2023
200 2001 2012
200 2001 2011
200 2002 2018
200 2002 2019


to this:

p0 p1 p2
------------ ------------ ------------
100
1000
1
3
200
2000
2021
4
5
2001
2023
2012
2011
2002
2018
2019

Any suggestions would be useful (this is just to display the data "prettily" for the client). I had thought of using union statements, as well as just entering blank fields on the select statements. I know that this could be done much easier using a reporting tool, but i would like to keep away from one and let the database do the heavy lifting.

Thanks in advance,

JC

 

[vongrunt]

I'd say this is wrong kind of heavy lifting for database...

Do you want to do it anyway?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[SQLBill]

As vongrunt says...this isn't really something that should be done by the database. You should have this done by the front-end application (and Query Analyzer really isn't intended to be used as a front-end application).

-SQLBill

Posting advice: FAQ481-4875

 

[jecairns]

Yes, I know what you mean. I'd like to do that anyways, yes. Just less things to build into the app.

 

[jecairns]

Sorry, I should clarify - not to be saved in the db as such, just displayed ie.(from a temp table). Just if there's such a way that select statement(s) can output the data as above, without much data manipulation.

Thanks,

JC

 

[vongrunt]

If nothing else, interesting brain food...

select
	case when grouping(p0) <> grouping(p1) then convert(varchar(4), p0) else '' end as p0str,
	case when grouping(p1) <> grouping(p2) then convert(varchar(4), p1) else '' end as p1str,
	case when grouping(p2) =  0 then convert(varchar(4), p2) else '' end as p2str
from blah
group by p0, p1, p2
with rollup
-- having grouping(p0)=0
order by p0, p1, p2

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[jecairns]

Thanks for the help, this has definitely got me off in the right direction, vongrunt!!

JC