Add 'Export as Markdown' option to SEDE

Question

Now that Markdown tables are a thing, can we please have an option in the Stack Exchange Data Explorer to export the data in a format that's ready to paste in a Stack Exchange post?

I mean, I can do ugly things like this, and check the 'Text-only results' option:

which produces

Question	Score
A Terms of Service update restricting companies that scrape your profile information without your permission	2770
Firing mods and forced relicensing: is Stack Exchange still interested in cooperating with the community?	2405
Stack Overflow is doing me ongoing harm; it's time to fix it!	2060
FAQ for Stack Exchange sites	1798
Could we please be a bit nicer to new users?	1787

But it shouldn't be too difficult to add another export option to make our lives easier.

Answer 1

It will take some effort to implement this, specially if you also want to magic links to work.

Until this is done I'll offer this small helper query to produce a basic table quickly. I took your example query and fiddled a bit with the information_schema and its Columns table. That will give for any table the columns. I used that to build up an union statement, similar to yours but without having to define the columns, it uses the columns in a result table.

In my example I used a #temp table and therefor I query the tempdb.information_schema. But if you only have a simple projection over an existing table you can use the information_schema from the current database.

Adding or renaming a column in the #result table is now automatically reflected in the generated table.

Here is the example query

-- build a #result table

select top 5
       id
     , owneruserid
     , creationdate
     , score
     , viewcount as [my col name]
into #result  -- temp table
from posts
order by id

-- generate  markdown table 

declare @sql nvarchar(max)

select @sql = 'select ''|' + string_agg(column_name, '|') + '|'' as tbl' 
+  ' union all select ''|' + string_agg('-'        , '|') + '|'' as hdr'
+  ' union all select concat(''|'',' 
                           + string_agg(quotename(column_name), ',''|'',') 
                                                          + ',''|'') as row
     from #result' -- temp table
from tempdb.information_schema.columns -- in tempdb
where table_name like '#result%'       -- our temp table name

-- print @sql
exec (@sql)

And here is what the table looks like it generates:

id	owneruserid	creationdate	score	my col name
1	1	Jun 28 2009 7:14AM	65	3213
6	41673	Jun 28 2009 8:40AM	10	629
9	6309	Jun 28 2009 8:58AM	8	293
10	11361	Jun 28 2009 9:16AM	13	712
11	84671	Jun 28 2009 9:19AM	4	279