T-SQL help

Question

T-SQL help

etirem

SSCommitted

Points: 1655
More actions
April 16, 2012 at 11:57 am

#254831

Hi,
Can you please help me in getting the result set in a different way. I used CROSS JOIN but it is not working for me.
Below is the sample data which is followed by the required outcome. Thanks in advance!
IF OBJECT_ID('TempDb..#Temp1') IS NOT NULL
DROP TABLE #Temp1
CREATE TABLE #Temp1(SourceA varchar(5), SourceB varchar(5), Counts int)
INSERT INTO #Temp1
VALUES
('A','A',5909)
,('A','D',5418)
,('B','A',354)
,('B','B',32399)
,('B','C',3606)
,('B','D',5434)
,('B','E',14630)
,('C','A',5121)
,('C','C',29202)
,('C','D',29072)
,('C','E',3460)
,('D', 'D',34738)
,('E','A',623)
,('E','D',4998)
,('E','E',21406)
SELECT * FROM #Temp1
DROP TABLE #Temp1
I need the result set as:
SOURCEASOURCEBCOUNTS
AA5909
AD5418
AE623
AC5121
AB354
BA354
BB32399
BC3606
BD5434
BE14630
CA5121
CC29202
CD29072
CE3460
CB3606
DD34738
DE4998
DC29072
DB5434
DA5418
EA623
ED4998
EE21406
EC3460
EB14630

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76998 More actions · Answer 1

You're looking for a UNION, although you're duplicating a lot of your data by doing so. I'm not sure why you would want to do this.

SELECT SourceA, SourceB, Counts

FROM #Temp1

UNION

SELECT SourceB, SourceA, Counts

FROM #Temp1

I also don't know how they're sorted, so I didn't specify a sort.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

etirem SSCommitted Points: 1655 More actions · Answer 2

I want compare A vs B and create a graph out of it on Counts...thank you!!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

How do you arrive at the order of the results? what are teh rules for it?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Somehting like this?

SELECT SourceA = ISNULL(TabA.SourceA , TabB.SourceA)

,SourceB = ISNULL(TabA.SourceB,TabB.SourceB)

,Counts = ISNULL (TabA.Counts , TabB.Counts)

FROM

(SELECT T.SourceA , T.SourceB , T.Counts

FROM #Temp1 T ) TabA

FULL OUTER JOIN

(SELECT T.SourceB AS SourceA , T.SourceA AS SourceB, T.Counts

FROM #Temp1 T ) TabB

ON TabA.SourceA = TabB.SourceA

AND TabA.SourceB = TabB.SourceB

ORDER BY SourceA

Output

[/url]

drew.allen SSC Guru Points: 76998 More actions · Answer 5

Here's an improved version of my original query. It only scans the table once instead of twice.

SELECT u.SourceA, u.SourceB, t.Counts

FROM #Temp1 AS t

CROSS APPLY(

SELECT SourceA, SourceB

UNION

SELECT SourceB, SourceA

) AS u

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76998 More actions · Answer 6

As a note of comparison, ColdCoffee's approach requires four scans of the table and requires a total of 32 logical reads, whereas my queries only require one logical read per table scan.

Drew

Edited to include further details of the comparison.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

drew.allen (4/16/2012)
Here's an improved version of my original query. It only scans the table once instead of twice.
SELECT u.SourceA, u.SourceB, t.Counts
FROM #Temp1 AS t
CROSS APPLY(
SELECT SourceA, SourceB
UNION
SELECT SourceB, SourceA
) AS u
Drew

That's clever, Drew.. Fantastic. Right into the tool-box, that code!

drew.allen SSC Guru Points: 76998 More actions · Answer 8

I should issue a caveat on that code. It is NOT necessarily equivalent to the original query. They will produce the same results if the data includes each pair once (regardless of the order), but will produce different results when the data includes multiples of some pairs.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA