Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Beginner Expand / Collapse
Author
Message
Posted Saturday, August 25, 2012 12:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 27, 2012 5:22 AM
Points: 7, Visits: 15
I have a table in this format:
Origin Destination Tonnage(Kton)
Alabama Arizona 0.0012
Alabama Idaho 1.2
Idaho Alaska 644
Arizona Alaska 665
.........
Consisting of all the 50 states of USA as origin and destination
Is it possible that from this table i can get data like this:
Origin Destination KTon
Alabama Arizona 0.0012
Arizona Alabama 788
Alabama Idaho 1.2
Idaho Alabama 666
Idaho Alaska 644

Alaska Idaho 456
....
ie, Examples with destinations acting as an origin and origin acting as a destination


One possibility that i thought was
If in the original data i filter it with respect to each origin ie. a table for one origin and hence around 50 tables
and then perform a sql query which related the origin of one to destination of other and gives tonnage values for every example but this will take me a lot of time as for every 2 tables i need to write an sql statement hence 50P2(Permutation) possibilities.
If some one could help in this respect then i'll be obliged
Post #1350020
Posted Saturday, August 25, 2012 1:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:59 AM
Points: 990, Visits: 2,217
Your desired output doesn’t match the test data. Should we assume that, for example, “Arizona Alabama 788” is also in the original table?
I’m also not clear about 50 tables. Do you have 1 table or 50? Are you thinking about creating 50 tables and populating them with data from one original table?
Post #1350022
Posted Saturday, August 25, 2012 1:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 27, 2012 5:22 AM
Points: 7, Visits: 15
hey sorry forgot to mention this before
arizona alabama 788 is also there in original data
But lets say there is
arizona alabama 0 i.e tonnage value=0
data also there
so what i want is not to show this data in the output
ie. if there is no tonnage value in the return direction then output table should not have such values
Also
the original table is randomly arranged with the data
what i want is the output to be only in this format
Arizona Alabama 788
Alabama Arizona 34
ie. consecutively arranged in this way.

Post #1350023
Posted Saturday, August 25, 2012 2:12 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:59 AM
Points: 990, Visits: 2,217
OK, I don't have time right now but here is the setup that you should have provided in your first post. Hopefully someone will help. If not, i'll take stab at it Monday.


CREATE TABLE #Trips(
Origin VARCHAR(50)
,Destination VARCHAR(50)
,Tonnage FLOAT
);

INSERT INTO #Trips(Origin, Destination, Tonnage)
VALUES
('Alabama', 'Arizona', 0.0012),
('Alabama', 'Idaho', 1.2),
('Idaho', 'Alaska', 644),
('Arizona', 'Alaska', 665),
('Arizona', 'Alabama', 788),
('Alabama', 'Texas', 12),
('Idaho', 'Alabama', 666),
('Alaska', 'Idaho', 456),
('Alaska', 'Idaho', 0),
('Arizona', 'Michigan', 0)


SELECT * FROM #Trips;



DROP TABLE #Trips;


BTW, what should happen with one way trips like the Texas above?
Post #1350025
Posted Saturday, August 25, 2012 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 27, 2012 5:22 AM
Points: 7, Visits: 15
Actually the data consist of tonnage from each and every origin to each and every destination .... For the return trip from Texas a tonnage value of 0 will be stored as actually there was no return trip ... In this kind of data I want output like the one I specified before ... Thnx for the help
Post #1350053
Posted Saturday, August 25, 2012 12:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 1,886, Visits: 18,542
to try and help out...I'll throw the following code into the conversation

it may or may not be appropriate...but hopefully OP will reply with some code that helps us understand.


USE [TEMPDB] -- safe place
GO

-- go and create some test data to play with

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND type in (N'U'))
DROP TABLE [dbo].[Location]
GO

CREATE TABLE [dbo].[Location](
[LocationID] [varchar](1) NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Location]([LocationID])
SELECT N'A' UNION ALL
SELECT N'B' UNION ALL
SELECT N'C' UNION ALL
SELECT N'D' UNION ALL
SELECT N'E'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trips]') AND type in (N'U'))
DROP TABLE [dbo].[Trips]
GO


CREATE TABLE [dbo].[Trips](
[Origin] [varchar](1) NULL,
[Destnation] [varchar](1) NULL,
[Tonnage] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Trips]([Origin], [Destnation], [Tonnage])
SELECT N'A', N'B', 5 UNION ALL
SELECT N'A', N'D', 2 UNION ALL
SELECT N'B', N'A', 3 UNION ALL
SELECT N'B', N'E', 7 UNION ALL
SELECT N'B', N'A', 5 UNION ALL
SELECT N'B', N'D', 2 UNION ALL
SELECT N'C', N'A', 1 UNION ALL
SELECT N'C', N'A', 18 UNION ALL
SELECT N'D', N'C', 12 UNION ALL
SELECT N'D', N'A', 5 UNION ALL
SELECT N'D', N'E', 6 UNION ALL
SELECT N'A', N'B', 12 UNION ALL
SELECT N'D', N'C', 30 UNION ALL
SELECT N'E', N'D', 100 UNION ALL
SELECT N'D', N'E', 50 UNION ALL
SELECT N'E', N'C', 12 UNION ALL
SELECT N'E', N'A', 15

--do some work to start playing with

;WITH cte AS
(
SELECT A.LocationID AS Origin ,
B.LocationID AS Destination
FROM
Location A INNER JOIN Location B ON A.LocationID <> B.LocationID

)

SELECT CTE.Origin, CTE.Destination, SUM(COALESCE (Trips.Tonnage, 0)) AS TOTALS
FROM CTE LEFT OUTER JOIN
Trips ON CTE.Destination = Trips.Destnation AND CTE.Origin = Trips.Origin
GROUP BY CTE.Origin, CTE.Destination
HAVING (SUM(COALESCE (Trips.Tonnage, 0)) > 0)
ORDER BY CTE.Origin, CTE.Destination



edit to exclude zero tonnage trips


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1350058
Posted Saturday, August 25, 2012 12:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
shikhar_0511 (8/25/2012)
Actually the data consist of tonnage from each and every origin to each and every destination .... For the return trip from Texas a tonnage value of 0 will be stored as actually there was no return trip ... In this kind of data I want output like the one I specified before ... Thnx for the help


Please write some insert statements that explain what the data looks like. Don't use real data, but mock up the real data types and structures.

Also, once you have sample data, then explain what it means.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350061
Posted Saturday, August 25, 2012 11:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
I am not sure but I think he is looking something like this ; and I am still scratching my head to do this in a proper way..

CREATE TABLE #Trips(
Origin VARCHAR(50)
,Destination VARCHAR(50)
,Tonnage FLOAT
);

INSERT INTO #Trips(Origin, Destination, Tonnage)
VALUES
('Alabama', 'Arizona', 0.0012),
('Alabama', 'Idaho', 1.2),
('Idaho', 'Alaska', 644),
('Arizona', 'Alaska', 665),
('Arizona', 'Alabama', 788),
('Alabama', 'Texas', 12),
('Idaho', 'Alabama', 666),
('Alaska', 'Idaho', 456),
('Alaska', 'Idaho', 0),
('Arizona', 'Michigan', 0)

CREATE TABLE #Trips1(
Origin VARCHAR(50)
,Destination VARCHAR(50)
,Tonnage FLOAT
);

declare @originA varchar(50),@DestinationA varchar(50),@originB varchar(50),@destinationB varchar(50),
@tonnageA float,@tonnageB float

declare trip_cursor cursor for

SELECT distinct a.origin as originA ,
a.destination As destinationA,
a.tonnage As tonnageA,
b.origin as originB,
b.destination as destinationB,
b.tonnage as tonnageB FROM #Trips a
outer apply #trips b
where a.Origin=b.Destination
and a.Destination=b.Origin
and a.tonnage<>0
and b.tonnage<>0

open trip_cursor

fetch next from trip_cursor into
@originA ,@DestinationA ,@tonnageA,@originB ,@destinationB
,@tonnageB
while @@FETCH_STATUS=0
begin
insert into #Trips1 values (@originA,@DestinationA,@tonnageA)

insert into #Trips1 values (@originB,@DestinationB,@tonnageB)

fetch next from trip_cursor into
@originA ,@DestinationA ,@tonnageA,@originB ,@destinationB
,@tonnageB
end

close trip_cursor
deallocate trip_cursor
insert into #Trips1 (origin,destination,tonnage) select origin,destination,tonnage from #Trips where Tonnage<>0
except select origin,destination,tonnage from #Trips1-- where tonnage <>0
select * from #Trips1
drop table #Trips1


still looking for a good solution; it's weekend :P


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1350091
Posted Sunday, August 26, 2012 8:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
shikhar_0511 (8/25/2012)
hey sorry forgot to mention this before
arizona alabama 788 is also there in original data
But lets say there is
arizona alabama 0 i.e tonnage value=0
data also there
so what i want is not to show this data in the output
ie. if there is no tonnage value in the return direction then output table should not have such values
Also
the original table is randomly arranged with the data
what i want is the output to be only in this format
Arizona Alabama 788
Alabama Arizona 34
ie. consecutively arranged in this way.



I've worked with truck shipments and other delivery logistics/tracking before. You really need a truck number and a delivery date in the data. Do you have those in the data? If not, this project is pretty much dead because you'll never be able to correlate and sort the data properly.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1350114
Posted Sunday, August 26, 2012 7:45 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
Jeff is probably right, of course.

However I thought I'd try this using a quirk I've seen in CROSS APPLY VALUES with respect to the order of the results that it returns.

So let's try this:

CREATE TABLE #Trips
(Origin VARCHAR(50), Destination VARCHAR(50), Tonnage FLOAT);

INSERT INTO #Trips(Origin, Destination, Tonnage)
VALUES
('Alabama', 'Arizona', 0.0012),
('Alabama', 'Idaho', 1.2),
('Alabama', 'Alaska', 12),
('Idaho', 'Alaska', 644),
('Idaho', 'Alabama', 666),
('Idaho', 'Arizona', 667),
('Arizona', 'Alaska', 665),
('Arizona', 'Alabama', 788),
('Arizona', 'Idaho', 588),
('Alaska', 'Idaho', 456),
('Alaska', 'Arizona', 0),
('Alaska', 'Alabama', 0)

;WITH CTE AS (
SELECT c.Origin, c.Destination, c.Tonnage
,n=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM #Trips a
CROSS APPLY #Trips b
CROSS APPLY (
VALUES (a.Origin, a.Destination, a.Tonnage)
,(b.Origin, b.Destination, b.Tonnage)) c(Origin, Destination, Tonnage)
WHERE (a.Origin = b.Destination AND b.Origin = a.Destination))
SELECT Origin, Destination, Tonnage
FROM (
SELECT Origin, Destination, Tonnage
FROM CTE a
WHERE EXISTS (
SELECT 1
FROM CTE b
WHERE b.n < a.n AND b.Origin = a.Origin AND b.Destination = a.Destination)) b
--WHERE Tonnage <> 0

DROP TABLE #Trips


This returns these results:

Origin	Destination	Tonnage
Idaho Alabama 666
Alabama Idaho 1.2
Arizona Alabama 788
Alabama Arizona 0.0012
Arizona Idaho 588
Idaho Arizona 667
Alaska Idaho 456
Idaho Alaska 644
Alaska Arizona 0
Arizona Alaska 665
Alaska Alabama 0
Alabama Alaska 12



Which aside from the 0 tonnage values appears to be the sort order you want (or at least pretty darn close). The truly odd thing is that when I uncomment the WHERE Tonnage not equal 0, the query returns more rows than the above.

There may be some way to rearrange the WHERE so this doesn't happen and actually eliminates the rows you want to remove but I'm clueless as to why it would return more rows.

Someone will come along no doubt and say "quirks in CROSS APPLY VALUES aside, row ordering is not guaranteed," and I'd probably agree. Just thought I'd throw this into the mix.

Jeff: Excuse the hidden RBAR. Just having some fun.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1350176
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse