Trying to format SQL query results

Question

Found this query here on Stack Overflow which I found very helpful to pull all table names and corresponding columns from a Microsoft SQL Server Enterprise Edition (64-bit) 10.50.4286 SP2 database.

SELECT   o.Name, c.Name
FROM     sys.columns c 
JOIN     sys.objects o ON o.object_id = c.object_id 
WHERE    o.type = 'U' 
ORDER BY o.Name, c.Name

It produces a table with two columns like this, each row has the table name in column 01 and the corressponding columns in column 02:

enter image description here

What I really want however is something like this, one column for each table name and the tables columns listed below it like this:

enter image description here

I've already started doing this manually in Excel, but with over 5000 rows returned it would be really nice if there was a way to format the results in the query itself to look like this. Thanks in advance!

The first example is listing table name and column name for user tables (that's the `o.type='U'` condition). But we have no idea what the four columns you want are. Please clearly state what data you want. — Richard, Jun 25 '15 at 14:17
@ Amit - I'm running the query in SQL Server management Studio 2012, then select all in the results and copy with headers and paste into Excel. So I guess the answer is yes, I would like the end result in Excel, but i don't need code to do it automatically - hope that answers your question. @ Richard - I still want all the tables and columns, I just was hoping to have the output in SQLSMS2012 to display it in the format in the second pick so I don't have to reorganize manually - thought maybe there was a way to modify the query or ad formatting code to the query to do this. Thanks forreplying. — n00b, Jun 25 '15 at 15:53
SQL is designed to work in sets. It operates on and "generates" tabular data (There are exceptions, and ways to do more than that, but that's the bread & butter). What you're trying to do is NOT tabular. It's relatively easy to do in Excel which is your target output. No reason to do that with SQL. — Amit, Jun 25 '15 at 19:31

score 3 · Accepted Answer · answered Jun 26 '15 at 08:40

As everyone is telling you, this is an un-SQL-y thing to do. Your resultset will have an arbitrary number of columns (equal to the number of user tables in your database, which could be huge). Since the resultset must be rectangular, it will have as many rows as the maximum number of columns in any of your tables, so many of the values will be NULL.

That said, a straightforward dynamic PIVOT gets you what you want:

DECLARE @columns nvarchar(max);
DECLARE @sql nvarchar(max);

SET @columns = STUFF ( ( 
                   SELECT '],[' + t.name 
                   FROM sys.tables t 
                   WHERE t.type = 'U' 
                   FOR XML PATH('') ), 1, 2, '') 
               + ']';

SET @sql = '
    SELECT ' + @columns + ' 
    FROM
    ( 
        SELECT   t.Name tName
                 , c.Name cName
                 , ROW_NUMBER() OVER (PARTITION BY t.Name ORDER BY c.Name) rn
        FROM     sys.columns c 
        JOIN     sys.tables t ON t.object_id = c.object_id 
        WHERE    t.type = ''U'' 
    ) raw
    PIVOT (MAX(cName) FOR tName IN ( ' + @columns + ' )) 
    AS pvt;
    ';

EXECUTE(@sql);

This is what it produces on my master database:

spt_fallback_db     spt_fallback_dev    spt_fallback_usg    spt_monitor     MSreplication_options
------------------- ------------------- ------------------- --------------- ----------------------
dbid                high                dbid                connections     install_failures
name                low                 lstart              cpu_busy        major_version
status              name                segmap              idle            minor_version
version             phyname             sizepg              io_busy         optname
xdttm_ins           status              vstart              lastrun         revision
xdttm_last_ins_upd  xdttm_ins           xdttm_ins           pack_errors     value
xfallback_dbid      xdttm_last_ins_upd  xdttm_last_ins_upd  pack_received   NULL
xserver_name        xfallback_drive     xfallback_vstart    pack_sent       NULL
NULL                xfallback_low       xserver_name        total_errors    NULL
NULL                xserver_name        NULL                total_read      NULL
NULL                NULL                NULL                total_write     NULL

(11 row(s) affected)

This is exactly what I needed, thank you! I understand this isn't a usual SQL query, reason I'm doing this is the database is for a Software Management Tool and contains very good computer audit info, but no one really knows or can find documentation on what the tables/columns are for. So I plan on adding columns to this spreadsheet with sample data hoping it will make it easier to determine what each column is and what we could use it for. This saved me hours of manual Excel manipulation. — n00b, Jun 26 '15 at 17:28

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

It might be easiest to do for example something like this:

Build a comma separated list using for XML path, for example like this.
Then copy that result to excel and use data to columns to create separate columns from the items
Use copy + paste special -> transpose to turn rows into columns

edited May 23 '17 at 12:32

Community

1
1

answered Jun 25 '15 at 14:19

James Z

12,209
10
24
44

Thanks for the reply - not sure if this would work for me, I only have read access to the database and this is an app production DB. Does the link you provided require creating a new table in the database? Or does that just modify the table the query produces to display results? I'm hesitant to create a new table directly in the database and not even sure i have the rights to do so if that is what that code is doing. – n00b Jun 25 '15 at 16:09

Trying to format SQL query results

2 Answers2