SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Beginner


SQL Beginner

Author
Message
shikhar_0511
shikhar_0511
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
rVadim
rVadim
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1397 Visits: 2308
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?
shikhar_0511
shikhar_0511
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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.
rVadim
rVadim
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1397 Visits: 2308
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?
shikhar_0511
shikhar_0511
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11669 Visits: 37442
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
and remember....every day is a school day

Steve Jones
Steve Jones
SSC Guru
SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: Administrators
Points: 140525 Visits: 19415
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
My Blog: www.voiceofthedba.com
demonfox
demonfox
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2119 Visits: 1192
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 Tongue

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205367 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16937 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search