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