SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Simple(?) Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ApolloC
Starting Member

3 Posts

Posted - 08/03/2012 :  13:31:33  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/03/2012 :  15:25:56  Show Profile  Reply with Quote
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/

Go to Top of Page

ApolloC
Starting Member

3 Posts

Posted - 08/03/2012 :  18:12:52  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/03/2012 :  22:32:28  Show Profile  Reply with Quote
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/

Go to Top of Page

ApolloC
Starting Member

3 Posts

Posted - 08/06/2012 :  15:04:34  Show Profile  Reply with Quote
Perhaps it is outside of our SQL abilities because it appears to count the records as opposed to displaying the value. Thanks anyway.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/06/2012 :  15:14:13  Show Profile  Reply with Quote
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/

Go to Top of Page

Betah
Starting Member

South Africa
1 Posts

Posted - 08/20/2012 :  03:43:20  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/20/2012 :  11:14:27  Show Profile  Reply with Quote
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/

Go to Top of Page

Jared Drake
Starting Member

4 Posts

Posted - 09/01/2012 :  22:06:37  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000