sead.j (7/2/2008)

---

Basic requirement is to use subqueries, but loops are alowed too...

I can post DDL and sample data but isn't it easier way just to post Northwind MDF and LDF...

If you would post your DDL and some sample data, it would be easier for us to help you.

Create some temp tables and some insert statements with sample data.:)

See here...

A comma is the same as a cross join.

It returns a cartesian product. 😉

Personally, I don't see how it is possible using a nested subquery.

If you do find an...

Jeff Moden (6/26/2008)

---

Ummm... sure... here's the original script...

The example I gave is a modified example of what we are doing.

I thought the actual example would take too long to explain.

Does anyone have any suggestions of how to...

Take a look at this post: http://www.sqlservercentral.com/Forums/Topic519719-338-1.aspx

Since you are using SQL Server 2005 you can use a PIVOT.

declare @cust_product table

(cust varchar(10), prod varchar(10))

insert into @cust_product VALUES ('A','P')

insert into @cust_product VALUES ('A','Q')

insert into @cust_product VALUES ('A','P')

insert...

I've seen the same issue solved as follows:

You could have a table that stores a global unique id. Start it with a value of 1.

Create a stored procedure that returns...

Question: What's with all of the Left Join (Select * from #Report) ?

Is that any different than doing a join like this:

Select

SUM(ISNULL(R1.Users,0)) ...

How about this?

;WITH OrganizationUsers AS

(

SELECT U.Organization

,R.Users

,R.[Month]

,R.[Year]

FROM #Users U

JOIN #Report R ON U.UserAlias = R.UserAlias

)

SELECT [Year]

, [Month]

, [Org1]

, [Org2]

, [Org3]

FROM OrganizationUsers

PIVOT( SUM(Users) FOR Organization IN ([Org1], [Org2], [Org3])) AS OrganizationPivot

GROUP BY...

Can you please post your DDL and some sample data?

That will make it a lot easier to answer your question. 😉

Some more links that may be helpful:

http://aspadvice.com/blogs/jlovell/archive/2004/09/15/1992.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50945

balamurugan.ganesan (5/26/2008)

---

Hi,

I need to load a csv file in to SQL 2005 which has comma within the data. How to load this I am fed up trying this:angry:

Some help on...

Matt, your example misses a row.

My example returns one row for each minute, yours misses one minute.

Your Results:

ActionTimeactionName

1900-01-01 12:40:51.000BUY

1900-01-01 12:41:41.000SELL

1900-01-01 12:42:01.000BUY

1900-01-01 12:42:41.000SELL

1900-01-01 12:42:51.000BUY

My Results:

ActionTimeActionName

01:42BUY

12:40BUY

12:41BUY

12:41SELL

12:42BUY

12:42SELL

How about this:

DECLARE @Actions TABLE

(

ActionTime DATETIME

,ActionName VARCHAR(10)

)

INSERT INTO @Actions

SELECT '12:40:01' ,'BUY' UNION ALL

SELECT '12:40:31' ,'BUY' UNION ALL

SELECT '12:40:51' ,'BUY' UNION...