Formatting link lists using TSQL

Question

Shog9 keeps on making my link lists look awesome.

Essentially, I write a bunch of queries that pull out results from the Stackoverflow data dump. However, my link lists look very ugly and are hard to understand.

Using some formatting magic Shog9 manages to make the link lists look a lot nicer.

So, for example, I will write a query that returns the following:

question id,title,user id, other info
4,When setting a form’s opacity should I use a decimal or double?,8,Eggs McLaren, some other stuff lots of text

And I want it to paste it into an answer on meta and make it look like this:

  Question Id                                      User Name         Other Info

When setting a form’s opacity...  Eggs Mclaren   Some other stuff...

So assuming my starting point is the query that returns the start info.

What are the least amount of steps I can run in query analyser to turn the results into:

<h3>&nbsp;&nbsp;Question Id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;User Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Other Info </h3>
<pre>
<a href="http://stackoverflow.com/questions/4"&gt;When setting a form’s opacity...</a>  <a href="http://stackoverflow.com/users/8/eggs-mclaren"&gt;Eggs Mclaren</a>   Some other stuff...
</pre>

My initial thoughts are to insert the results into a temp table and then run a stored proc that will iron the data into my desired structure. Run the proc, cut and paste and be done.

Any candidate TSQL based solutions to this problem?

EDIT: Accepting my answer, its the only solution with an implementation.

Answer 1

Not sure of your exact requirements, but have you considered selecting the data as XML and then applying an XSLT transform to the results?

Answer 2

You could do something like:

with 
data (question_id,title,user_id, username ,other_info) as 
(
select 4,'When setting a form''s opacity should I use a decimal or double?',8,'Eggs McLaren', 'some other stuff lots of text'
union all
select 5,'Another q title',9,'OtherUsername', 'some other stuff lots of text')
select 
    (select 'http://stackoverflow.com/questions/' + cast(question_id as varchar(10)) as [@href], title as [*] for xml path('a')) as questioninfo
    ,(select 'http://stackoverflow.com/users/' + cast(user_id as varchar(10)) + '/' + replace(username, ' ', '-') as [@href], username as [*] for xml path('a')) as userinfo
    , other_info
from data

...but see how you go. I personally find that FOR XML PATH is very powerful for getting marked-up results in a way that suits me.

Rob

Answer 3

I'll update this post with my progress as I refine my proc:

Example:

select top 20
    UserId = u.Id, 
    UserName = u.DisplayName,
    u.Reputation,
    sum(case when p.ParentId is null then 1 else 0 end) as Questions, 
    sum(case when p.ParentId is not null then 1 else 0 end) as Answers 
into #t
from Users u
join Posts p on p.OwnerUserId = u.Id 
where p.CommunityOwnedDate is null and p.ClosedDate is null
group by u.Id, u.DisplayName, u.Reputation
having sum(case when p.ParentId is not null then 1 else 0 end) < sum(case when p.ParentId is null then 1 else 0 end) / 6
order by Reputation desc 

exec spShog9

Results:

User           Reputation Questions Answers

Edward Tanguay 8317 465 24 
me             5767 311 29 
Joan Venge     4844 226 14 
Blankman       4546 310 1  
acidzombie24   4359 371 32 
Thanks         4350 416 21 
Masi           4193 555 74 
LazyBoy        3230 94  12 
KingNestor     3187 92  11 
Nick           2084 79  6  
George2        1973 263 1  
Xaisoft        1944 174 12 
John           1929 160 24 
danmine        1901 53  3  
zsharp         1771 145 16 
carrier        1742 56  8  
JC Grubbs      1550 50  5  
vg1890         1534 56  2  
Coocoo4Cocoa   1514 143 0  
Keand64        1513 83  5  
Masi           4193 555 74 
LazyBoy        3230 94  12 
KingNestor     3187 92  11 
Nick           2084 79  6  
George2        1973 263 1  
Xaisoft        1944 174 12 
John           1929 160 24 
danmine        1901 53  3  
zsharp         1771 145 16 
carrier        1742 56  8  
JC Grubbs      1550 50  5  
vg1890         1534 56  2  
Coocoo4Cocoa   1514 143 0  
Keand64        1513 83  5

The proc is on gist: http://gist.github.com/165544