I need to generate a report where the column names are defined by the result
s
of the first query and the data by the second query.
The closest I can get is to create a stored procedure that creates a temp
table with named columns fill it with a single row (query 1) and then fill i
t
with data. I then query the table for my results to pass on to the
application dropping the table when done. My issue with this method is that
App I am passing data to shows the temp column names as well (no way to
suppress them).
Over simplified pseudo code:
CREATE TABLE ##c (
ordr int,
Col1 varchar(30),
Col2 varchar(30)
)
INSERT INTO ##c
SELECT ‘1’ AS [ordr], h.header1, h.header2
FROM dbo.configure h
WHERE h.id = ‘105’
INSERT INTO ##c
SELECT ‘2’ AS [ordr], d.data1, d.data2
FROM dbo.detail d
WHERE d.id = ‘105’
ORDER by d.data1
SELECT col1, col2
FROM ##c
ORDER BY ordr, col1, col2
DROP TABLE ##cYou could generate a SELECT string you need with column names from the first
query and rename them use the AS keyword.
Then you can use EXECUTE (T-SQL command) to execute it.
Search the BOL for "Using EXECUTE with a Character String"
Milan
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:B43F7817-1C89-44AF-A4EE-15FCC0F17B52@.microsoft.com...
>I need to generate a report where the column names are defined by the
>results
> of the first query and the data by the second query.
> The closest I can get is to create a stored procedure that creates a temp
> table with named columns fill it with a single row (query 1) and then fill
> it
> with data. I then query the table for my results to pass on to the
> application dropping the table when done. My issue with this method is
> that
> App I am passing data to shows the temp column names as well (no way to
> suppress them).
> Over simplified pseudo code:
> CREATE TABLE ##c (
> ordr int,
> Col1 varchar(30),
> Col2 varchar(30)
> )
> INSERT INTO ##c
> SELECT '1' AS [ordr], h.header1, h.header2
> FROM dbo.configure h
> WHERE h.id = '105'
> INSERT INTO ##c
> SELECT '2' AS [ordr], d.data1, d.data2
> FROM dbo.detail d
> WHERE d.id = '105'
> ORDER by d.data1
> SELECT col1, col2
> FROM ##c
> ORDER BY ordr, col1, col2
> DROP TABLE ##c
>|||You can do something like the following:
IF OBJECT_ID (N'teble1') IS NOT NULL
DROP FUNCTION table1
GO
select * into table1 from Query1 where 0 = 1
insert into table1 select * from Query2
"Dan" <Dan@.discussions.microsoft.com> wrote in message news:B43F7817-1C89-44AF-A4EE-15FCC0F
17B52@.microsoft.com...
>I need to generate a report where the column names are defined by the resul
ts
> of the first query and the data by the second query.
> The closest I can get is to create a stored procedure that creates a temp
> table with named columns fill it with a single row (query 1) and then fill
it
> with data. I then query the table for my results to pass on to the
> application dropping the table when done. My issue with this method is tha
t
> App I am passing data to shows the temp column names as well (no way to
> suppress them).
> Over simplified pseudo code:
> CREATE TABLE ##c (
> ordr int,
> Col1 varchar(30),
> Col2 varchar(30)
> )
> INSERT INTO ##c
> SELECT '1' AS [ordr], h.header1, h.header2
> FROM dbo.configure h
> WHERE h.id = '105'
> INSERT INTO ##c
> SELECT '2' AS [ordr], d.data1, d.data2
> FROM dbo.detail d
> WHERE d.id = '105'
> ORDER by d.data1
> SELECT col1, col2
> FROM ##c
> ORDER BY ordr, col1, col2
> DROP TABLE ##c
>|||Do you mean:
Declare @.c1 as varchar(30)
Set @.c1 = Select col1 from table1
Select newCol1 AS @.c1 from table2
if so I can not get that to work
"Milan Kosanovic" wrote:
> You could generate a SELECT string you need with column names from the fir
st
> query and rename them use the AS keyword.
> Then you can use EXECUTE (T-SQL command) to execute it.
> Search the BOL for "Using EXECUTE with a Character String"
> Milan
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:B43F7817-1C89-44AF-A4EE-15FCC0F17B52@.microsoft.com...
>
>|||I am missing somthing hear as well:
I can not get my query clauses to run within the framework laid out.
"fish" wrote:
> You can do something like the following:
> IF OBJECT_ID (N'teble1') IS NOT NULL
> DROP FUNCTION table1
> GO
> select * into table1 from Query1 where 0 = 1
> insert into table1 select * from Query2
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message news:B43F7817-1C89-
44AF-A4EE-15FCC0F17B52@.microsoft.com...
>
>|||Have you ever considered being a good programmer who does his reports
in the front end instead of writing kludges in the database? This is
the whole idea of a tiered architecture.
Have you ever thought about writing declarative code instead mimicking
the steps of a procedural program? You are even trying to name a table
the way we used to name a tape on a mag tape system.|||> Have you ever considered being a good programmer who does his reports
> in the front end instead of writing kludges in the database? This is
> the whole idea of a tiered architecture.
IMHO report writing is a simple and boring task for 95% of time. IMHO
using a good programmer for report writing is an overkill - in my
recent experience a high scholl senior quickly mastered Crystal Reports
and
did all that booooooooring label aligning pretty well, and for a cheap
price. Overall it was a faster and cheaper approach as opposed to the
traditional one...|||Requestor
Character string
Trust me this is not my preferred method but the client’s dictated report
application and the predefined legacy applications db structure are off
limits to me. And I have this one obstacle to getting sign off. My boundarie
s
are that the I can pass a string to the db and the grid returned is what the
user see’s
I have figured out that I can
declare @.col1 as Varchar(15)
declare @.sql as varchar(755)
set @.col1 = (select some stuff)
Set @.sql = 'create table#t ([' + @.col1 + ] varchar(30))'
exec (@.sql)
not great but it gets me out the door within the clients constraints
"Alexander Kuznetsov" wrote:
> IMHO report writing is a simple and boring task for 95% of time. IMHO
> using a good programmer for report writing is an overkill - in my
> recent experience a high scholl senior quickly mastered Crystal Reports
> and
> did all that booooooooring label aligning pretty well, and for a cheap
> price. Overall it was a faster and cheaper approach as opposed to the
> traditional one...
>
LISM:
