Quicky SQL Question for you DBA types

Factual Questions
1

Here’s what I’m looking for.

Back in the day when I was using a UNIX based database (Sybase to be exact) and all my queries were done entriely on the command line within a Telnet style window it was often helpful to change the way query results were displayed.

There was a command, not sure if it was a T-SQL command or if it was a UNIX command but when you’d run a query like this:


SELECT * from user_table WHERE username = 'Omniscient'

…instead of getting the typical results that looked like this:


username    member_type     posts  coolness  wit
----------  --------------  -----  --------  ------
Omniscient  Charter Member  8571   Super     Rapier

You’d get results that looked like this:


username: Omniscient
member_type: Charter Member
posts: 8571
coolness: Super
wit: Rapier

Can anyone tell me how that’s done? I’m using SQL Server 7.0 in Windows these days, and the queries are submitted using the Query Analyzer and that’s where I want to accoplish this today. Ideas? If its different in SQL Server than it was using Sybase, just for the sake of my curiosity how’d I used to do that too?

2

Huh… that’s cool, but I’ve never heard of a simple way to do it in SQL server… you could probably do a workaround by loading the fields into ‘t-sql’ variables and building your output with print statements.

declare @mtype varchar(30), @pc int

select @mtype = member_type, @pc = posts from user_table where username = ‘chrisken’

print "member_type: " + @mtype
print "points: " + cast(@pc as varchar(8))

Very awkward though.

3

I’m afraid I don’t know SQL Server, and the answer is very much dependent on your person-to-SQL interface. I’m used to Oracle 8i/9i with SQL Plus, and I’m not quite sure off the top of my head how to format output in that manner - at least, not without writing a fancy PL/SQL procedure.

I will say if you did the query through Excel via it’s ODBC features, you could basically swap rows for columns without too much trouble… but I think you’re shooting for:

<record1>
<record1.info>

<record2>
<record2.info>

right?

4

I’m thinking it might have been a flag you could set at the end of the SQL command, UNIX/DOS style, and it could possibly have been feature specific to the Client we used (the name of which eludes me right now).

It was exceedingly useful when I wanted to output and print detail reports of a small set of items that had too many columns to make a wide format practical.

As it is I’ve been using the Excel Transpose paste to get to the same result but it’s pretty tedious and it seems there must be a better way, why bother c/ping the raw text into excel, go text-to-columns, copy, paste special, etc…

Not to mention the fact that my email client tends to choke on pasting in excel data if you use plain text email, very annoying.

I knew I could write a little procedure to do this, but it’s mainly useful for quick one-off glances at data so that is more effort than its really worth. It was so convienent to have it available in the old program I assumed that most of the established ones would have the same type of function. Quick and easy…
Still hoping it’s one of those lesser known things that one of you might know about.

5

Just curious: What would the results have looked like for a query that got three rows of results, say, instead of one? Three records, I mean.

6

There is probably no one unified way to do what you ask across all SQL servers. You’d have to look at the documentation for each one to see if something like what you ask is available.

To do the thing you ask in MySQL is to terminate the query with \G instead of a semicolon. I don’t know about any others.

7

If you have something like Crystal handy, the easiest way is to probably whip up a fast report. Just put the columns vertically into the detail section, and print away.

I’ve never seen it in MS SQL Server, and I just tabbed to what I was working on to see if there was an option on the Results tab that would do that, but there wasn’t.

About the only thing that I can think of to do using pure T-SQL in MS SQL Server 7.0 takes a bit more work than what you’re talking about, but is at least doable. Might be worthwhile if you run the same queries many times. Try something like this, if that is the case:



SELECT
    'username: ' + username + CHAR(10) +
    'member_type: ' + member_type + CHAR(10) +
    'posts: ' + posts + CHAR(10) +
    'coolness: ' + coolness + CHAR(10) +
    'wit: ' + wit + CHAR(10)
FROM
    user_table
WHERE
    user_name = 'Omniscient'
8

PostgreSQL, with the psql command line interface, uses the \x meta-command.

The results would look something like:



- [ RECORD 1 ] -
FIELD1  |  DATA
FIELD2  |  DATA

- [ RECORD 2 ] -
FIELD1  |  DATA
FIELD2  |  DATA

- [ RECORD 3 ] -
FIELD1  |  DATA
FIELD3  |  DATA
9

:smack: Here’s the closing tag:
10

I may whip up a quick VB utility to generate reports like that, just to see how easy it is. :wink: (I already have a large collection of visual basic programs to pull results from a SQL server database into various different formats. :slight_smile: )

11

I did find out how you did it in Sybase, at least.



   1> SELECT * from user_table WHERE username = 'Omniscient'
   2> go -m vert
12 


Private Sub Command1_Click()
    Dim conn As New Connection
    conn.Open "Provider=SQLOLEDB.1;Password=" & txtPass & ";Persist Security Info=True;User ID=" & txtLogin & ";Initial Catalog=" & txtDBase & ";Data Source=" & txtServer & ";Connect Timeout=12"

    conn.CommandTimeout = 900

    Dim rst As Recordset
    
    On Error GoTo queryerror
    
    Set rst = conn.Execute(Text1)
    
    On Error GoTo 0
    
    Dim s As New clsString, rc As Integer, j As Integer
    
    Do While Not rst.EOF
    
        rc = rc + 1
        
        s.Add " - [ RECORD " & rc & " ] - " & vbCrLf
    
        For j = 1 To rst.Fields.Count
        
            s.Add rst.Fields(j - 1).Name & ": " & rst.Fields(j - 1).Value & vbCrLf
        
        Next

        rst.MoveNext
        
        s.Add vbCrLf
        
    Loop
    
    Text2.Text = s.Value
    
    Set rst = Nothing
    
    conn.Close
    Set conn = Nothing
    
    MsgBox "Done"
    
    Exit Sub
        
queryerror:
    
    MsgBox "There was an error with your query: " & Err.Description
       
    Set rst = Nothing
    
    conn.Close
    
    Set conn = Nothing
 
    
End Sub

may not be relevant for the OP, but I like it hehehehe

13

If these queries are all ad hoc, and if you must rely on a T-SQL only solution, I did think of one more way. Initial development would take a little bit of time to get just right, but once that’s done, it should be pretty easy to use repeatedly.

Create a stored proc that grabs the fields from the syscolumns table based on a passed table name, which then runs a cursor through them, adding code to make it match my above code, turning this whole thing into a string.

Then you can execute it dynamically.

14

However, that doesn’t allow (easily) for the use of multiple-table join queries, and would be pretty much helpless with calculated query fields.

Is there any way to use t-sql to analyze the fields in the result set of an arbitrary query, or do you need to operate at a remove and do that at the ADO (or equivalent) level, like I did??

15

There are several methods to handle the more complex things that you’re talking about, so determining what sort of queries are typically run would make a big difference.

For multi-table selects, calculated fields, derived tables, complex where clauses, etc., I’d probably throw the output into a table, then parse that to create the big string. Basically bring it back to the simplicity of a simple select before doing the programmatic processing.