help eliminating temp table

help eliminating temp table

Post by Andr » Sat, 13 Apr 2002 14:48:55



sql2k

I have a query that I'd like some SELECT help with.  I'm trying to eliminate
using a temp table and do everything in one SELECT statement.

Here is my situation:  I select several fields from multiple tables, one of
the fields needs to concatenate the results from 1-n rows.  For example:

SELECT
    fields...
    Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName +
';' + EMail1 + ';'
FROM...

The issue is that I want "ALL" the rows concatenated into the Param field,
not just the current row.  My resultset currently looks like this:



What I really want is:



I can get this if I insert my Param field into a temp table then do the
following:

SELECT  DISTINCT
 Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName + ';'
+ EMail1 + ';'
INTO #TempCaseNotify
FROM  Tables...




FROM #TempCaseNotify


I'd like to eliminate the use of a temp table if possible.  Anyone have a
suggestion on how I can do this?  I'm sure it's a nested select but I've
tried a couple and they're not working.

Thanks, Andre

 
 
 

help eliminating temp table

Post by Tibor Karasz » Sat, 13 Apr 2002 16:36:16


Andre,

I guess you are looking for a derived table:

SELECT * FROM
 (SELECT ... FROM ...) AS x
WHERE...

Note that you have to give the inner/derived table an alias name.
You can also name the columns in the derived table:

SELECT * FROM
 (SELECT ... FROM ...) AS x(c1, c2)
WHERE...

If you don't, columns named will be picked from the inner query (possible unnamed).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> sql2k

> I have a query that I'd like some SELECT help with.  I'm trying to eliminate
> using a temp table and do everything in one SELECT statement.

> Here is my situation:  I select several fields from multiple tables, one of
> the fields needs to concatenate the results from 1-n rows.  For example:

> SELECT
>     fields...
>     Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName +
> ';' + EMail1 + ';'
> FROM...

> The issue is that I want "ALL" the rows concatenated into the Param field,
> not just the current row.  My resultset currently looks like this:



> What I really want is:



> I can get this if I insert my Param field into a temp table then do the
> following:

> SELECT  DISTINCT
>  Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName + ';'
> + EMail1 + ';'
> INTO #TempCaseNotify
> FROM  Tables...




> FROM #TempCaseNotify


> I'd like to eliminate the use of a temp table if possible.  Anyone have a
> suggestion on how I can do this?  I'm sure it's a nested select but I've
> tried a couple and they're not working.

> Thanks, Andre


 
 
 

help eliminating temp table

Post by Ivan Arjentinsk » Sat, 13 Apr 2002 22:36:11


Andre,

First let me point you that the

technique is undocumented and there are examples where it fails. So you are
on your own. You could find the examples where the technique fails, by
searching this newsgroup - author is Umachandar.

Now, knowing this you could try the following (I didn't test it in any way):



FROM (
    SELECT  DISTINCT
     Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName +
';'
    + EMail1 + ';'
    FROM  Tables...
) X

--
Ivan Arjentinski
-------------------------------------------------------------
Please include CREATE TABLE and INSERT INTO scripts in your post so we don't
have to guess about schema and enter sample data manually
-------------------------------------------------------------

> sql2k

> I have a query that I'd like some SELECT help with.  I'm trying to
eliminate
> using a temp table and do everything in one SELECT statement.

> Here is my situation:  I select several fields from multiple tables, one
of
> the fields needs to concatenate the results from 1-n rows.  For example:

> SELECT
>     fields...
>     Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName
+
> ';' + EMail1 + ';'
> FROM...

> The issue is that I want "ALL" the rows concatenated into the Param field,
> not just the current row.  My resultset currently looks like this:



> What I really want is:



> I can get this if I insert my Param field into a temp table then do the
> following:

> SELECT  DISTINCT
>  Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName +
';'
> + EMail1 + ';'
> INTO #TempCaseNotify
> FROM  Tables...




> FROM #TempCaseNotify


> I'd like to eliminate the use of a temp table if possible.  Anyone have a
> suggestion on how I can do this?  I'm sure it's a nested select but I've
> tried a couple and they're not working.

> Thanks, Andre

 
 
 

help eliminating temp table

Post by Andr » Sun, 14 Apr 2002 01:52:12


This still returns 3 individual rows, instead of the desired 1 row.  Am I
doing something wrong?

An example of what I want to do is in the following query.  I'd like to have
these 2 records returned in the same row, and only return 1 record.

USE Northwind
SELECT * FROM
 ( SELECT  Combined = CustomerID + ';' + ContactName + ';'
  FROM  Customers
  WHERE CustomerID IN ('ALFKI','ANATR')
  ) AS X

This returns:

Combined
-------------------------------------
ALFKI;Maria Anders;
ANATR;Ana Trujillo;

I want:

Combined
-------------------------------------
ALFKI;Maria Anders;ANATR;Ana Trujillo;

Also keep in mind that I can have 1-n records returned.

Thanks, Andre



Quote:> Andre,

> I guess you are looking for a derived table:

> SELECT * FROM
>  (SELECT ... FROM ...) AS x
> WHERE...

> Note that you have to give the inner/derived table an alias name.
> You can also name the columns in the derived table:

> SELECT * FROM
>  (SELECT ... FROM ...) AS x(c1, c2)
> WHERE...

> If you don't, columns named will be picked from the inner query (possible
unnamed).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...




- Show quoted text -

> > sql2k

> > I have a query that I'd like some SELECT help with.  I'm trying to
eliminate
> > using a temp table and do everything in one SELECT statement.

> > Here is my situation:  I select several fields from multiple tables, one
of
> > the fields needs to concatenate the results from 1-n rows.  For example:

> > SELECT
> >     fields...
> >     Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' +
FirstName +
> > ';' + EMail1 + ';'
> > FROM...

> > The issue is that I want "ALL" the rows concatenated into the Param
field,
> > not just the current row.  My resultset currently looks like this:



> > What I really want is:



> > I can get this if I insert my Param field into a temp table then do the
> > following:

> > SELECT  DISTINCT
> >  Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName +
';'
> > + EMail1 + ';'
> > INTO #TempCaseNotify
> > FROM  Tables...




> > FROM #TempCaseNotify


> > I'd like to eliminate the use of a temp table if possible.  Anyone have
a
> > suggestion on how I can do this?  I'm sure it's a nested select but I've
> > tried a couple and they're not working.

> > Thanks, Andre

 
 
 

help eliminating temp table

Post by Andr » Sun, 14 Apr 2002 01:53:54


Thanks for letting me know about potential pitfalls with this - I wasn't
aware of that.  I actually got that suggestion here in the newsgroups on
something else I was working on a while back!

I'd prefer to stay away from something that could be problematic.

Andre


> Andre,

> First let me point you that the

> technique is undocumented and there are examples where it fails. So you
are
> on your own. You could find the examples where the technique fails, by
> searching this newsgroup - author is Umachandar.

> Now, knowing this you could try the following (I didn't test it in any
way):



> FROM (
>     SELECT  DISTINCT
>      Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName
+
> ';'
>     + EMail1 + ';'
>     FROM  Tables...
> ) X

> --
> Ivan Arjentinski
> -------------------------------------------------------------
> Please include CREATE TABLE and INSERT INTO scripts in your post so we
don't
> have to guess about schema and enter sample data manually
> -------------------------------------------------------------


> > sql2k

> > I have a query that I'd like some SELECT help with.  I'm trying to
> eliminate
> > using a temp table and do everything in one SELECT statement.

> > Here is my situation:  I select several fields from multiple tables, one
> of
> > the fields needs to concatenate the results from 1-n rows.  For example:

> > SELECT
> >     fields...
> >     Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' +
FirstName
> +
> > ';' + EMail1 + ';'
> > FROM...

> > The issue is that I want "ALL" the rows concatenated into the Param
field,
> > not just the current row.  My resultset currently looks like this:



> > What I really want is:



> > I can get this if I insert my Param field into a temp table then do the
> > following:

> > SELECT  DISTINCT
> >  Param = CAST(ClientID AS VARCHAR) + ';' + LastName + ', ' + FirstName +
> ';'
> > + EMail1 + ';'
> > INTO #TempCaseNotify
> > FROM  Tables...




> > FROM #TempCaseNotify


> > I'd like to eliminate the use of a temp table if possible.  Anyone have
a
> > suggestion on how I can do this?  I'm sure it's a nested select but I've
> > tried a couple and they're not working.

> > Thanks, Andre

 
 
 

help eliminating temp table

Post by Steve Dassi » Sun, 14 Apr 2002 04:45:27



Quote:> Thanks for letting me know about potential pitfalls with this - I wasn't
> aware of that.  I actually got that suggestion here in the newsgroups on
> something else I was working on a while back!

> I'd prefer to stay away from something that could be problematic.

Don't be so easily brainwashed.

Execution Plan and Results of Aggregate Concatenation Queries
Depend Upon Expression Location (Q287515)
http://support.microsoft.com/support/kb/articles/Q287/5/15.ASP

Marriage is problematic.But people still do it:)

Check out RAC v2.0 and ObjectScriptr at:
www.rac4sql.com

 
 
 

help eliminating temp table

Post by Andr » Sun, 14 Apr 2002 05:22:23


Love the marriage comment:)  Coming from a happily married guy eh?

It would appear to me that since I don't do any order by's, I should be ok -
based on the link.  Am I stuck with a temp table if I want to do an
aggregate concatenation?

Thanks, Andre




> > Thanks for letting me know about potential pitfalls with this - I wasn't
> > aware of that.  I actually got that suggestion here in the newsgroups on
> > something else I was working on a while back!

> > I'd prefer to stay away from something that could be problematic.

> Don't be so easily brainwashed.

> Execution Plan and Results of Aggregate Concatenation Queries
> Depend Upon Expression Location (Q287515)
> http://support.microsoft.com/support/kb/articles/Q287/5/15.ASP

> Marriage is problematic.But people still do it:)

> Check out RAC v2.0 and ObjectScriptr at:
> www.rac4sql.com

 
 
 

help eliminating temp table

Post by Steve Dassi » Sun, 14 Apr 2002 06:23:51



Quote:> Love the marriage comment:)  Coming from a happily married guy eh?

> It would appear to me that since I don't do any order by's, I should be
ok -
> based on the link.  Am I stuck with a temp table if I want to do an
> aggregate concatenation?

Well if your on S2k you have use of table variables.
But in general experiment.
Think of any query as a blind date:)
 
 
 

help eliminating temp table

Post by Andr » Sun, 14 Apr 2002 07:28:36


Are you leading me to a function - with the table variable?  I have no
problem with that, and that's what I was originally thinking.  I just
wondered if I could to do an aggregate concatenation as part of my select
statement.

Andre




> > Love the marriage comment:)  Coming from a happily married guy eh?

> > It would appear to me that since I don't do any order by's, I should be
> ok -
> > based on the link.  Am I stuck with a temp table if I want to do an
> > aggregate concatenation?

> Well if your on S2k you have use of table variables.
> But in general experiment.
> Think of any query as a blind date:)

 
 
 

help eliminating temp table

Post by Steve Dassi » Sun, 14 Apr 2002 10:26:00



Quote:> Are you leading me to a function - with the table variable?  I have no
> problem with that, and that's what I was originally thinking.  I just
> wondered if I could to do an aggregate concatenation as part of my select
> statement.

Well just remember all server udf's are laid back:)
As for using a select statement to this over rows you
can't but you can.
Check out Relational Application Companion for S2k at:
www.rac4sql.com

An RAD tool for laid back users:)

 
 
 

1. HOWTO: eliminating temp tables

I have a sql problem which I am unable to solve.  I am hoping that
someone can enlighten me.  My situation is thus:

T1

id    type    value  <many, many more fields>
---------------------------------------------
42       1      123
43       0      124
45       0      125
46       1      126

T2

id    type    value  <many, many more fields>
---------------------------------------------
98       1      124

I have figured out that with an outer join, I can get this:

WHERE (T1.type = 1) AND T1.value *= T2.value

T1.id  T1.type  T1.value   T2.id  T2.type  T2.value
---------------------------------------------------
42           1       123      98        1       124
46           1       126  <NULL>   <NULL>    <NULL>

What I am looking for is all the T1's that do NOT have a corresponding
T2.

Currently, someone else created a stored procedure that uses three
temp tables:

SELECT id, value INTO #TT1 FROM T1 WHERE type = 1
SELECT id, value INTO #TT2 FROM T2 WHERE type = 1
SELECT id INTO #TT3 FROM #TT1 where #TT1.value NOT IN ( SELECT * FROM
#TT2)
SELECT T1.id, T1.type, T1.value, <other fields in T1> from T1, #TT3
WHERE T1.id = #TT3.id

I have figured out how to cut it down to one temp table (I think):

SELECT T1.id as T1_id, T2.id as T2_id INTO #TT4 FROM T1, T2 WHERE
T1.type =1 AND T1.value *= T2.value
SELECT T1.id, T1.type, T1.value, <other fields in T1> from T1, #TT4
WHERE T1.id = #TT4.id AND #TT4.T2_id IS NULL

Can this be done without any temp tables?  The database is MS SQL
2000.

Sam

P.S.  Is there a better source of information which might help me
solve this problem?  I do have the "SQL for Smarties" book and was
unable to find a solution there.

2. How Can I Exceed 2GB .mdb Limit?

3. howto eliminating temp tables

4. Selective insertion with ADO

5. Temp table vs Global Temp table

6. Javascript access to SQLCA

7. Help trying to eliminate a full table scan and optimize query

8. SQL case statement

9. HELP: Deadlocking system tables (aggregate functions, temp tables, select into)

10. need help...Using @TABLE variable instead of #temp tables with dynamic SQL (sp_executesql)

11. eliminating rows in table a that appear in table b

12. temp table problem with global temp option

13. Massive Updates: Temp Or Not Temp Tables?