Setting Results Header - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Setting Results Header

Author

Topic

Spetty
Starting Member

25 Posts

Posted - 2006-11-30 : 11:42:31

Is there a way to tell SQL to set the header as another table's FIELD name?

for example, and I know this is wrong
select field.table AS (select otherfield.othertable from othertable)....

Is there a way to grab on specific result and have it display as a column header?

Essentially I have two tables:

Table 1 headers
Information1 | Information2 | Information3 | Information4
5/12/1979 | ... | ... | ...

Table 2 headers
Information1 | Information2 | Information3 | Information4
DOB | test1 | other2 | etc4

Basically table2 is a header template for the information in table 1, howver the two are not linked in any way. Can I take the 'DOB' from table 2 and set it as the header when pulling information from table1.information1?

Thanks!
Shawn

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 12:08:59

You'll need to create the SELECT statement with dynamic SQL like this

create table table1 (
Information1 datetime, 
Information2 varchar(20), 
Information3 varchar(20), 
Information4 varchar(20))
create table table2 (
Information1 varchar(20), 
Information2 varchar(20), 
Information3 varchar(20), 
Information4 varchar(20))
insert table1 values (getdate(), 'one', 'two', 'three')
insert table1 values (getdate(), 'four', 'five', 'six')
insert table1 values (getdate(), 'seven', 'eight', 'nine')
insert table2 values ('DOB', 'test1', 'other2', 'etc4')
GO
declare @sql nvarchar(4000)
set @sql = ''
SELECT @sql = 'SELECT Information1 AS [' + Information1 
	+ '], Information2 AS [' + Information2 
	+ '], Information3 AS [' + Information3 
	+ '], Information4 AS [' + Information4
	+ '] FROM Table1'
FROM Table2
exec sp_executesql @sql

Spetty
Starting Member

25 Posts

Posted - 2006-11-30 : 12:34:48

quote:

SELECT @sql = 'SELECT Information1 AS [' + Information1 
	+ '], Information2 AS [' + Information2 
	+ '], Information3 AS [' + Information3 
	+ '], Information4 AS [' + Information4
	+ '] FROM Table1'
FROM Table2
exec sp_executesql @sql

how would that translate as a SQL Query? No coding involved, just strictly Query in this case.

select (select table1.information1 as [' + information1 + '], from table1) from table2

gives me a syntax error.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 13:22:03

It doesn't translate into a "SQL query" as you put it. You have to create the SQL query as string and then execute that query dynamically as I showed you, you can't execute the result of one query in another query without doing it dynamically. That's why you got an error.

Spetty
Starting Member

25 Posts

Posted - 2006-11-30 : 14:11:33

quote:
Originally posted by snSQL

It doesn't translate into a "SQL query" as you put it. You have to create the SQL query as string and then execute that query dynamically as I showed you, you can't execute the result of one query in another query without doing it dynamically. That's why you got an error.

Thanks, I was hoping it could be done as the Query is all I can go off of at the moment with the nature of the program. I appreciate the help!

Shawn

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-30 : 14:20:07

Can the program call a stored procedure at least, then you could put that code in a stored procedure and call the stored procedure. Otherwise you may well be able to just put all that code into the command that the program uses (where you would put a query) and it will probably work too.

Spetty
Starting Member

25 Posts

Posted - 2006-12-01 : 08:58:03

What about using CASE Select to accomplish this in a Query, would it be possible going that route?

I understand what you are saying about the stored procedure, however this is something we are trying to do without being able to change the code of our program. This is simply trying to pull information out of the DB for displaying with a SQL Query.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-02 : 01:20:16

You can use a CASE statement to use various values for the columns but the name of a column has to be a literal, not a variable or expression, so you have to do it with dynamic SQL. You can also build the statement dynamically in your client code if that's better than running the dynamic code on the server.

When you say "This is simply trying to pull information out of the DB for displaying with a SQL Query." where are you doing that, because you may be able to execute the script I gave you anyway?

Spetty
Starting Member

25 Posts

Posted - 2006-12-02 : 11:41:47

quote:
Originally posted by snSQL

You can use a CASE statement to use various values for the columns but the name of a column has to be a literal, not a variable or expression, so you have to do it with dynamic SQL. You can also build the statement dynamically in your client code if that's better than running the dynamic code on the server.

When you say "This is simply trying to pull information out of the DB for displaying with a SQL Query." where are you doing that, because you may be able to execute the script I gave you anyway?

We use a program that works like Query Analyzer to execute report/result style queries on our DB, however it also needs to be able to function in an MSDE environment as well, so we would necessarily have the ability of full SQL.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-02 : 20:24:41

So a tool like that must be able to send a simple batch like I gave you and MSDE will be quite happy with that. You don't need to restrict yourself to single statement queries, batches of statements are fine.

Spetty
Starting Member

25 Posts

Posted - 2006-12-03 : 12:27:01

I do want to thank you for trying to explain how to accomplish this.

I did take your entire statement and ran it in our "Analyzer", and it runs successfully. However it is still not giving me any results to "view" in our analyzer. It does certainly work in Query Analyzer.

How would I get the results to view, much like a standard select query? I was reading on using OUTPUT as a possibility, but am unsure of how it would work in this case.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-03 : 19:41:35

Are you executing all of this together as a single batch?

declare @sql nvarchar(4000)
set @sql = ''
SELECT @sql = 'SELECT Information1 AS [' + Information1 
	+ '], Information2 AS [' + Information2 
	+ '], Information3 AS [' + Information3 
	+ '], Information4 AS [' + Information4
	+ '] FROM Table1'
FROM Table2
exec sp_executesql @sql

If so and it's not working, are you getting any error messages?

That should work in almost any client so without knowing more about your application it's hard for me to say what you have to do. You could try monitoring with SQL Profiler to see exactly what your app does send when you execute that code. One thing you might try is to put the statements all on a single line with semi colons between them, something like this (abbreviated for readability, but shows all the places to put semi-colons)

declare @sql nvarchar(4000); set @sql = ''; SELECT @sql 'SELECT ... FROM Table1' FROM Table2; exec sp_executesql @sql

Spetty
Starting Member

25 Posts

Posted - 2006-12-03 : 22:26:59

Query Analyzer - Runs perfectly and displays results as they should

Our "Analyzer" - Runs perfectly, says statement executed successfully -1 record effected.

Actually, finally tested in our Reporting Module (different than our analyzer), and it does work perfectly and gives us the results.

The only thing we need to be able to add at this point is errortrapping for when table.information# would happen to be null.

Thanks much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources