# Formatting Data Explorer Table Results

**URL:** <https://meta.discourse.org/t/formatting-data-explorer-table-results/277939>\
**Category:** Data & reporting\
**Tags:** data-explorer, sql-tutorial\
**Created:** [September 5, 2023, 11:37pm UTC](https://meta.discourse.org/t/formatting-data-explorer-table-results/277939 "2023-09-05T23:37:33Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [September 5, 2023, 11:37pm UTC](https://meta.discourse.org/t/formatting-data-explorer-table-results/277939/1 "2023-09-05T23:37:33Z")

</div>

In Discourse, you have the ability to enhance the appearance of your [Data Explorer](https://meta.discourse.org/t/32566) query results.

Formatting table results in the [Data Explorer](https://meta.discourse.org/t/32566) can be done by using special syntax in your SQL queries, which allows you to display your data in a more intuitive and visually appealing manner, directly within the Discourse interface.

## Formatted Table Results

Here’s a quick overview of the syntax and data fields you can use to format your query results:

```sql
SELECT
-- To display a quote, user avatar/username, and a link to the post, use:
id AS post_id,

-- To display the topic title, the count of posts, and a link to the topic, use:
topic_id,

-- To display time in a relative format (hover for full date), use:
updated_at as reltime$updated_at,

-- To display a user avatar/username, and a link to the activity page, use:
user_id,

-- To display a group name and a link to the group, use:
(SELECT id FROM groups WHERE id = 12) AS group_id,

-- To display a category badge, name, and a link to the category, use:
(SELECT id FROM categories WHERE id = 22) AS category_id,

-- To display a badge icon, name, and a link to the badge, use:
(SELECT id FROM badges WHERE id = 2) AS badge_id,

-- To suppress special formatting and use only text, use:
topic_id AS text$topic_id_text,

-- To render a value as HTML, use:
'<h2>hello</h2>' AS html$html,

-- To treat a column as a URL (if named `xyz_url`), and name the link using the format `name,https://example.com`, use:
'Meta,https://meta.discourse.org' AS somecolumnname_url

FROM posts 
WHERE topic_id = 32566
LIMIT 1

```

Below is an example of how your query results might look when formatted using the above syntax:

 ![Formatted Query Results](https://global.discourse-cdn.com/meta/original/4X/7/6/c/76c47cf29c58a36cccd70dfa154823e7684315ad.png)

## Individual Examples

Let’s discuss some of these features in more detail:

### Post and Topic Links

You can display a `post_id` as a quote, user avatar/username, and a link to the post using `AS post_id`. You can also display a `topic_id` as a topic title, the count of posts, and a link to the topic using `AS topic_id`.

_Examples_:

```sql
SELECT 
    p.id AS post_id
FROM 
    posts p

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/e/5/3/e53d4bd368d2767119c15f36cbd03740b624e697.png)

```plaintext
SELECT 
    t.id AS topic_id
FROM 
    topics t

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/2/a/f/2af591851e1ed340eeafceab402ce92accac2291.png)

### Relative Time

The `AS reltime$updated_at` statement will display the a timestamp in a relative format. If you hover over a timestamp displayed in this format, you’ll also see the full date.

_Example_:

```sql
SELECT 
    id,
    updated_at AS reltime$updated_at
FROM
    posts
ORDER BY updated_at DESC

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/2/f/2/2f23655f95fbca631c82d3a6bd7ef3e862b5c65e.png)

### User, Group, Category, and Badge Links

You can display user avatars/usernames, group names, category names, and badges icons/names, all linked to their respective pages by using `AS user_id`, `AS group_id`, `AS category_id`, and `AS badge_id`.

_Examples_:

```sql
SELECT
    u.id AS user_id
FROM
    users u

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/3/a/5/3a5fe68d98a9369a7be9afa9bf3cd772744b48fc.png)

```sql
SELECT
    g.id AS group_id
FROM
    groups g

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/7/f/d/7fd2c612ea83adf87ce2e0a2f751726b11095ebf.png)

```sql
SELECT
    c.id AS category_id
FROM
    categories c

```

 ![](https://global.discourse-cdn.com/meta/original/4X/f/5/4/f5482b5b185a996eddccd3aaa787357b4472ad12.png)

```sql
SELECT
    b.id as badge_id
FROM
    badges b

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/5/4/a/54af7e80750ff4f03866b51c3b1f16ed190d88c7.png)

### Text and HTML Rendering

You can display HTML results as rendered HTML by using `AS html$html`. You can also suppress special formatting to use only text by using ` AS text$column_name`

_Example_:

```sql
SELECT 
    topic_id, 
    cooked AS html$html,
    topic_id AS text$topic_id_text
FROM
    posts
GROUP BY topic_id, cooked

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/f/8/e/f8e0fff804fe79b0bdbca66047bf3418cda9e921.png)

### URL Rendering

If you name a column as `xyz_url`, it will be treated as a URL. You also can name a URL link using the format `name,https://example.com`.

_Example_:

```sql
SELECT
    u.username,
    'https://example_site.com/u/' || u.username || '/summary' AS profile_url,
    'Profile Page,https://example_site.com/u/' || u.username || '/summary' AS profile_page_url
FROM
    users u
WHERE u.username LIKE 'anon%'

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/7/b/7/7b7e004743cb28be6455616a97a6df8d760aeba7.png)

## Automatic Formatting

There are also certain SQL fields in the [Data Explorer](https://meta.discourse.org/t/32566) that are automatically formatted. These include:

### Links

Any field that contains a URL will be automatically formatted as a clickable link.

_Example_:

```sql
SELECT 
    id, 
    remote_url
FROM remote_themes  

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/0/2/4/0242987edc8028d92b1fe9ef39ed43f4c91cabbc.png)

### IDs

IDs (like post IDs, topic IDs, user IDs, etc.) are often automatically linked to their respective entities when referenced in foreign tables.

_Example_:

```sql
SELECT 
    user_id
FROM 
    topics

```

 ![image](https://global.discourse-cdn.com/meta/original/4X/d/b/2/db2f0bc4442095892b512f36aa4e1ffdcd8a9433.png)

## More Topics in this Series

- [Data Explorer Tutorial - Part 1 - Writing Your First Query](https://meta.discourse.org/t/data-explorer-tutorial-part-1-writing-your-first-query/277925)
- [Data Explorer Tutorial - Part 2 - Discourse SQL Basics](https://meta.discourse.org/t/data-explorer-tutorial-part-2-discourse-sql-basics/277927)
- [Utilizing Parameters in Data Explorer Queries](https://meta.discourse.org/t/utilizing-parameters-in-data-explorer-queries/277934)
- **This Topic**

---

_[View the full topic](https://meta.discourse.org/t/formatting-data-explorer-table-results/277939)._