Display results VERTICALLY in Results window

Total article views: 293 | Views in the last 30 days: 22

 Rate this |   Join the discussion |  Briefcase

DESCRIPTION:
This procedure will allow you to display results vertically (down) instead of across the screen. It's great when you are dealing with a query that has 50 columns and just a couple of records.

LIMITATIONS:
* It will be very slow if you are trying to return a lot of records. Works best for queries returning 10 or less records.
* It displays the DATALENGTH for text and image fields

INSTRUCTIONS:
Basically build your query with as many inner joins as needed.

1. Load the sp_SHOWDOWN query into the master database or a local database.
2. Execute the following query: sp_SHOWDOWN 1
3. This will display syntax and the 3 lines you'll need to add to your query.
4. Modify the query as instructed and execute again
5. Results are now vertical!

EXAMPLE: (using pubs database)
IF OBJECT_ID('tempdb..#tempwide') IS NOT NULL DROP TABLE #tempwide

select top 1 a.au_id 'a.au_id', a.au_lname 'a.au_lname', a.au_fname 'a.au_fname', a.phone 'a.phone', a.address 'a.address', a.city 'a.city', a.state 'a.state', a.zip 'a.zip', a.contract 'a.contract',
ta.au_id 'ta.au_id', ta.title_id 'ta.title_id', ta.au_ord 'ta.au_ord', ta.royaltyper 'ta.royaltyper',
t.title_id 't.title_id', t.title 't.title', t.type 't.type', t.pub_id 't.pub_id', t.price 't.price', t.advance 't.advance', t.royalty 't.royalty', t.ytd_sales 't.ytd_sales', t.notes 't.notes', t.pubdate 't.pubdate'
INTO #tempwide
from authors a
inner join titleauthor ta ON a.au_id = ta.au_id
inner join titles t ON t.title_id = ta.title_id

EXEC sp_SHOWDOWN

I certainly didn't type all those field names in the SELECT above. I used _SELECT (also contributed by me) to produce unique fieldnames for each table

Please feel free to modify to fit your needs.

Total article views: 293 | Views in the last 30 days: 22