Trying to format SQL query results

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:

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

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!

---

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)

---

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

链接地址: http://www.djcxy.com/p/93848.html

上一篇: 如何从所有列查询max（len）？

下一篇: 尝试格式化SQL查询结果