Get which column values are same

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get which column values are same

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-11 : 06:10:39

Hi,

This is my two select statement.

SELECT * FROM City1 WHERE City1.City = @FromCity

SELECT * FROM City1 WHERE City1.City = @ToCity

ID City Line1 Line2 Line3 Line4 Line5 Line6
---------------------------------------------------
2 Salem NULL Yellow Green NULL Brown NULL

ID City Line1 Line2 Line3 Line4 Line5 Line6
-----------------------------------------------------
20 Idappadi Red NULL Green NULL Brown NULL

I am having from and tocity names.

I want to join the two select and get which cloums having the colors are same.

AS Line3 Green
Line5 Brown

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-11 : 06:13:07

No matter which column the color name is stored?

E 12°55'05.63"
N 56°04'39.26"

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-11 : 06:18:59

quote:
Originally posted by Peso

No matter which column the color name is stored?

E 12°55'05.63"
N 56°04'39.26"

No Matter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-11 : 06:37:32

[code]DECLARE @Sample TABLE
(
ID INT,
City VARCHAR(20),
Line1 VARCHAR(20),
Line2 VARCHAR(20),
Line3 VARCHAR(20),
Line4 VARCHAR(20),
Line5 VARCHAR(20),
Line6 VARCHAR(20)
)

INSERT @Sample
SELECT 2, 'Salem', NULL, 'Yellow', 'Green', NULL, 'Brown', NULL UNION ALL
SELECT 20, 'Idappadi', 'Red', NULL, 'Green', NULL, 'Brown', NULL

;WITH Yak (ID, theColor, theColumn)
AS (
SELECT u.ID,
u.theColor,
u.theColumn
FROM @Sample AS s
UNPIVOT (
theColor
FOR theColumn IN (s.Line1, s.Line2, s.Line3, s.Line4, s.Line5, s.Line6)
) AS u
)

SELECT y1.ID,
y2.ID,
y1.theColumn,
y1.theColor,
y2.theColumn,
y2.theColor
FROM Yak AS y1
INNER JOIN Yak AS y2 ON y2.ID > y1.ID
AND y2.theColor = y1.theColor[/code]

E 12°55'05.63"
N 56°04'39.26"

baburk
Posting Yak Master

108 Posts

Posted - 2008-12-19 : 02:07:20

quote:
Originally posted by Peso

Thanks Peso. You are genius.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources