How do I know if an index column is in descending order from SQL Server?

Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>> Hi Peter
>>
>> EXECUTE sp_MShelpindex N'authors', N'aunmind'
>>
>> try this in PUBS
>>
>> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>>
>>
>> --
>> kind regards
>> Greg O
>> Need to document your databases. Use the firs and still the best AGS SQL
>> Scribe
>> http://www.ag-software.com
>>
>>
>> "Peter Reid" <noreply@.microsoft.com> wrote in message
>> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>>
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>>
>> USE <db1>
>> select into a temp table
>>
>> USE <db2>
>> select into another temp table
>>
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>>
>> Thanks
>>
>>
>>
>>
>
>Hi Peter
Did you try:
EXECUTE pubs..sp_MShelpindex N'authors', N'aunmind'
John
"Peter Reid" wrote:
> Hey all that I want is to be able to run from another DB.
> This "sp_MShelpindex" like the "indexkey_property" only work running from
> the DB where the index sits.
> I need to join this information from multiple DBs in a single query, and I
> was wondering if (and how) this is possible.
> Thanks
> >
> > "GregO" <grego@.community.nospam> wrote in message
> > news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> >> Hi Peter
> >>
> >> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> >>
> >> try this in PUBS
> >>
> >> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
> >>
> >>
> >> --
> >> kind regards
> >> Greg O
> >> Need to document your databases. Use the firs and still the best AGS SQL
> >> Scribe
> >> http://www.ag-software.com
> >>
> >>
> >> "Peter Reid" <noreply@.microsoft.com> wrote in message
> >> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> >> How do I know if an index column is in descending order from SQL Server?
> >>
> >> I don't want to use "indexkey_property" as it doesn't work from another
> >> DB.
> >> I also know that I can do something like this:
> >>
> >> USE <db1>
> >> select into a temp table
> >>
> >> USE <db2>
> >> select into another temp table
> >>
> >>
> >> But what I'm actually interested in knowing is where this information is
> >> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> >> and furthermore how to query it.
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >
> >
>
>sql

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
ThanksHi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.c...cumented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2
,2,'IsDescending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.c...cumented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks

> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
Thanks
Hi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.co...umented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>
|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'IsDesc ending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>
|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.co...umented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>
|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks

> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
ThanksHi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'IsDescending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>> USE <db1>
>> select into a temp table
>> USE <db2>
>> select into another temp table
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>> Thanks
>>
>

How do I get number of fields returned by query?

Hi.

I am trying to get the results of a dynamic sql statement into a #table, in order to filter them. Given that I don't know how many fields will be returned, how do I accomplish this?

I believe I need to create the #table in advance, and then run the dynamic string as part of an insert [eg 'insert into #table exec (@.sql)' ], but in order to do this I need to know how many fields are going to be returned.

The results might also be returned by a procedure rather than a simple SQL statement, so I can't just parse @.sql to get the fields.

As an example,

declare @.sql nvarchar(200)

select @.sql = 'select "a" as ColA, "b" as ColB'

exec sp_executesql @.sql

returns two columns of data.

I think I need to get the results, count the fields, create the table and finally re-run the query with an insert to poulate the table. So how do I count the columns? And for bonus points, how do I get the column names?

Many thanks,

Neil Jones

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..)

|||

Thanks for your reply

Nitin Khurana wrote:

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

The problem here is that I can't edit the query. All I know at runtime is that it's a dynamic sql statement, which could be a simple select, or an execution of a stored procedure

Nitin Khurana wrote:

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

My question is, how do I get the number of columns?

Nitin Khurana wrote:

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..

Unfortunately it's not the number of rows that is the problem.

Cheers,

Neil Jones

|||

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

|||

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

|||

Try this:

SELECT * INTO #Temp
FROM (<Your SQL Query>) as T;

Note: If the columns returned by the query aren't properly named (no column name), this will not work. Otherwise the table will be created automatically with the column names from the query. Once the table ist created, you can access the Information about the columns from system views:

USE tempdb
GO
SELECT COUNT(*) FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name like '#Test%');

This will work with MS SQL Server 2005

Regards,

Paddy

|||

Here you go with a complete sample when having the statement as a variable:

USE AdventureWorks
GO

SET NOCOUNT ON;
GO

-- Create a Table for Testing
CREATE TABLE Test
( col1 INT NOT NULL
, col2 VARCHAR(20) NOT NULL
);
GO

-- Some useful Information
INSERT INTO Test VALUES (1, 'Test1');
INSERT INTO Test VALUES (2, 'Test2');
INSERT INTO Test VALUES (3, 'Test3');
INSERT INTO Test VALUES (4, 'Test4');
INSERT INTO Test VALUES (5, 'Test5');
INSERT INTO Test VALUES (6, 'Test6');
INSERT INTO Test VALUES (7, 'Test7');
INSERT INTO Test VALUES (8, 'Test8');
INSERT INTO Test VALUES (9, 'Test9');
INSERT INTO Test VALUES (10, 'Test10');
GO

-- The dynamic sql statement
DECLARE @.sql NVARCHAR(200);
SET @.sql = 'SELECT col2, col1 FROM Test';

-- Extend the statement for creating a temporary table (Note: Use Global Temporary Table (##TableName))
DECLARE @.sql2 NVARCHAR(250)
set @.sql2 = 'SELECT * INTO ##Test FROM (' + @.sql + ') AS T;';

-- Run the extended Statement
EXEC(@.sql2);
GO

-- View the result
SELECT * FROM ##Test;
GO

-- Get Information about the Temporary Table
USE tempdb
GO

-- Column Count
SELECT COUNT(*) FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- Names of Columns
SELECT name FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- CleanUp
USE AdventureWorks
GO

-- Drop the temporary Table
DROP TABLE ##Test;
GO

-- Drop the Test Data Table
DROP TABLE Test;
GO

SET NOCOUNT OFF;

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of you table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of your table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Lucky P wrote:

Here you go with a complete sample when having the statement as a variable:

That looks great, thanks!

I think I'm going to have to accept that it's not possible with all dynamic sql statements, such as "exec xp_proc1", and take what I can.

Cheers,

Neil

How do I get DESC order?

Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.

How do I keep it in DESC order for viewing? Here's the statement:

SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC

By the way, I'm using SQL Server Express, if it makes any difference.

|||

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

|||

Andrea Montanari wrote:

hi,

views, as tables, do not have an order at all.. in fact yoe are executing a SELECT ... FROM view .. ORDER BY ...;

this is the relevant (!) step in your requirement.. the order by clause is a cursor task, not associated with the underlying table/view.. data inside a table (on which the view is defined) is not ordered

please have a look at wikipedia and here as well, where you can read

"..A view is a relational table, and the
relational model states that a table is a set of rows. Since sets are not sorted - per definition - the rows in a view are not ordered either. .."

and even

".. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. .."

so, what you are getting, is correct..

if you like, you can "open" the view (in SSMSE) and apply the required "sort" clause adding the ORDER BY specs..

regards

Thank you for the very informative response as well as the links, Andrea. I was starting to go insane wondering what I was missing! Another day another lesson, I guess!

Once again, thanks for the info.

How do I find out what query someone ran?

Hello All,

We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?

Thanks in advance!

Kenny, your best bet is to fire up SQL Profiler and do some filtering so you only see the queries being executed by the application.

Thanks,
Sam Lester (MSFT)

how do I do this?

I want to change the order by criteria depending on a certain input paramater

So suppose

This is a simplified condensed version of what I need

declare @.sortID int
set @.sortID=1

So if the @.sortID=1 I wanna have this

select * from tblusersorder by name

else

select * from tblusers whereorder by age

so depending on the sort id the bolded text should change.

Only one way I know of ...

declare @.sortID int
set @.sortID=1
If (@.sortID = 1)
BEGIN
select * from tblusersorder by name
END
ELSE
BEGIN
select * from tblusers whereorder by age
END

I do not believe order by can be a parameter

|||

hello,

and other way to do, dynamic sql execution, some case is not good way to do but versstile

===============================

1declare @.idint2declare @.sqlnvarchar(max)34set @.id = 156set @.sql ='select top 10 productid, [name] from production.product '7+'order by ' +case @.id8when 1then'[name]'9when 2then'productid'10end1112execsp_executesql @.sql

===============================

|||busyweb is right. You could use dynamic SQL. I just never think of dynamic SQL because of the speed, security, maintainability problems. (smile)

How do I do this in SS2000?

Hi all,

I have a simple query which returns all the names of products attached to a particular order.

select a.orderid, b.product_name
from orders a, order_items b
where a.orderid = b.orderid

Say it returns this data: (sorry about the formatting!)

orderid product_name
===== =========
001234 Sweater (Black)
001234 Trousers (Large)
001234 T-Shirt (Pink)

What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".

I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..

TIA for any help...

MikeAre you prepared to use Analysis Services?|||Look at this. You may want to put it into a function, but this is the general idea

declare @.s varchar(8000)
select @.s = b.product_name + ', ' + COALESCE(@.s, '')
 from orders a, order_items b
 where a.orderid = b.orderid
if @.s is not null
 set @.s = substring(@.s, 1, LEN(@.s) - 1)
else
 set @.s = ''
select @.s

|||Thanks for that... that works fine but I need to fine tune it a bit. At the moment it gives me all products for all orders whereas I need it to give me just the product names for each unique order. I tried using a "GROUP BY a.orderid" but it won't let me use the product_name column in this way.

I also want to be able to select the columns I need from the first table such as orderid, order_date etc.

I've experimented with both but can't seem to really get it to work... a little more help would be much appreciated!

Cheers,

Mike

PS: pkr - no I can't really use Analysis Services as this is part of a stored procedure for a web app that also has to run on Oracle so it needs to be fairly standard ANSI SQL.|||Assuming you don't know how many products you've got for a an order its difficult to write a single query. This is my suggestion.
1. Create a temp table with the OrderID and a "csv" text column, defaulted to ''
2. Insert the unique set of orderids into the temp table
3. Run a query that UPDATEs the csv column with itself plus the "," + product name|||Thanks pkr... in the end I wrote a function which is passed the order id and reads the values of the products into a cursor. It then builds the string of product names and returns it. It seems to work very well and I've learnt quite a lot about SQL functions that I didn't know before. I'm not at work so I can't post it but I will on Monday so possibly someone in the future can see how to do this.

Thanks for the help.

Mike|||I use that code within a function and it works fine. Forgot to suggest that. I would stay away from cursors unless you have to use them. There is a significant performance hit. I'd use the query above and avoid the cursor.|||Replacing cursors is nearly always a good idea. However, be careful with funcs, you can basically end up doing the same thing as a cursor. If you code it "incorrectly" the function will run for each row in the set, therefore the perf will be like a cursor anyway!|||Luckily the table I run the function on will not have that many rows in it at one time. I also looked at the stats for a few orders and it does only seem to be reading the ones it needs rather than processing the whole table which is good.

Will post the function when I get to work today... then people can tell me if could do it any better.

Mike.