Simple(?) Query Help - 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)

Simple(?) Query Help

Author

Topic

ApolloC
Starting Member

3 Posts

Posted - 2012-08-03 : 13:31:33

I need to put a list of sales orders in a specific format to integrate with another application. I do not know how I would handle putting the items field into columns as opposed to rows. The number of columns could change every time you run the query.

Current Format
Sales Order #1 -- Item #1
Sales Order #1 -- Item #2
Sales Order #2 -- Item #1
Sales Order #2 -- Item #2
Sales Order #2 -- Item #3

Needed Format
Sales Order #1 -- Item #1 -- Item #2
Sales Order #2 -- Item #1 -- Item #2 -- Item #3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 15:25:56

you've try this i guess

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ApolloC
Starting Member

3 Posts

Posted - 2012-08-03 : 18:12:52

Thanks for the reply, but my IT guy who's better with SQL than I am showed me why this wouldn't work. This is summarizing data as a Pivot Table in Excel would. I am not trying to summarize data, I'm trying to change the format in which the data is displayed. Any other ideas out there?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 22:32:28

you've effectively summarizing it as you're trying to merge multiple rows to one. So it doesnt necessarily have to be summarizing values themselves

I still believe this is what you're looking at.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ApolloC
Starting Member

3 Posts

Posted - 2012-08-06 : 15:04:34

Perhaps it is outside of our SQL abilities because it appears to count the records as opposed to displaying the value. Thanks anyway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 15:14:13

quote:
Originally posted by ApolloC

Perhaps it is outside of our SQL abilities because it appears to count the records as opposed to displaying the value. Thanks anyway.

then replace COUNT with MIN or MAX with CASE condition inside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Betah
Starting Member

1 Post

Posted - 2012-08-20 : 03:43:20

I have created two temp table one was borrow and the other borrow return, with the following code; I did run it for the first time it runs and when I want to run it the second time I get the following errors

Drop table #temp_Borrow

SELECT
Date_Time,Username,Borrow_return,FID,Feature
INTO #temp_Borrow
FROM ArcGIS_log
WHERE Borrow_return='Borrow'

SELECT
Date_Time,Username,Borrow_return,FID,Feature,Return_date
INTO #temp_Borrow_return
FROM ArcGIS_log
WHERE Borrow_return='Borrow_return'

Select * From #temp_Borrow_return
Below is the errors that I received
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#temp_Borrow', because it does not exist or you do not have permission.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'ArcGIS_log'.

Please I need help how to fix this error and saving it.
I also need help in adding addition columns of hours used and utilization, both columns are not on the main table call ArcGIS_log

Bridget

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-20 : 11:14:27

please dont hijack threads. post your question as a new thread in future.

anyways solution for your prblem is


IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#temp_Borrow%' AND NOT LIKE '#temp_Borrow_Return%')
  Drop table #temp_Borrow 
IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#temp_Borrow_return%')
  Drop table #temp_Borrow_return


SELECT 
Date_Time,Username,Borrow_return,FID,Feature 
INTO #temp_Borrow
FROM ArcGIS_log
WHERE Borrow_return='Borrow' 

SELECT 
Date_Time,Username,Borrow_return,FID,Feature,Return_date 
INTO #temp_Borrow_return
FROM ArcGIS_log
WHERE Borrow_return='Borrow_return'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jared Drake
Starting Member

4 Posts

Posted - 2012-09-01 : 22:06:37

Maybe you should look into using views?

I am a writer for the http://www.afterhoursprogramming.com/index.php?article=167 (SQL section) on afterhoursprogramming.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources