Field Comparision - SQL Server Forums

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)

Field Comparision

Author

Topic

johnsi
Starting Member

10 Posts

Posted - 2008-01-08 : 04:35:37

Hi All

Im writing one stored procedure to compare each column values of one record to other. That is, Im having two rows from same table and i have to compare first row with second one column by column. If i any column value doesnot match i have to display that column name with values from both rows.
The concept is same like changelog
can u help

johnsi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 04:41:59

can you provide an example of what you want here ?

KH
[spoiler]Time is always against us[/spoiler]

johnsi
Starting Member

10 Posts

Posted - 2008-01-08 : 04:48:24

yes..
For eg,
Im having one table like this
Field1 Field2 Field3 Field4
1 2 1 2
1 1 3 4

now i have to compare first row with second one

And result must be like this

Field2 2 1
Field3 1 3
Field4 2 4

Thats is i need to display fieldname with records which is not matched

johnsi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 04:49:49

you only have 2 records in that table ? what if you have thousand of records ?

KH
[spoiler]Time is always against us[/spoiler]

johnsi
Starting Member

10 Posts

Posted - 2008-01-08 : 05:02:36

yes,
Im giving some condition that ll give two records only

johnsi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 05:21:38

[code]DECLARE @sample TABLE
(
Field1 int,
Field2 int,
Field3 int,
Field4 int
)
INSERT INTO @sample
SELECT 1, 2, 1, 2 UNION ALL
SELECT 1, 1, 3, 4

SELECT field = 'Field1', MIN(Field1), MAX(Field1) FROM @sample HAVING MIN(Field1) <> MAX(Field1)
UNION ALL
SELECT field = 'Field2', MIN(Field2), MAX(Field2) FROM @sample HAVING MIN(Field2) <> MAX(Field2)
UNION ALL
SELECT field = 'Field3', MIN(Field3), MAX(Field3) FROM @sample HAVING MIN(Field3) <> MAX(Field3)
UNION ALL
SELECT field = 'Field4', MIN(Field4), MAX(Field4) FROM @sample HAVING MIN(Field4) <> MAX(Field4)[/code]

KH
[spoiler]Time is always against us[/spoiler]

johnsi
Starting Member

10 Posts

Posted - 2008-01-08 : 06:08:27

THANKS...
THATS FOR TWO ROWS..
IF I HAVE MORE THAN TWO ROWS MEANS IS THERE ANY WAY?

johnsi

johnsi
Starting Member

10 Posts

Posted - 2008-01-15 : 02:43:20

Hi All
I want to the distinct rows from a table and then loop through the records.
Can you help me?

johnsi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-15 : 02:48:56

quote:
Originally posted by johnsi

THANKS...
THATS FOR TWO ROWS..
IF I HAVE MORE THAN TWO ROWS MEANS IS THERE ANY WAY?

johnsi

Well, you said only 2 records ?

quote:
Originally posted by johnsi

yes,
Im giving some condition that ll give two records only

johnsi

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-15 : 02:52:07

quote:
Originally posted by johnsi

THANKS...
THATS FOR TWO ROWS..
IF I HAVE MORE THAN TWO ROWS MEANS IS THERE ANY WAY?

johnsi

Please provide the sample data and how do you want the result to be.

KH
[spoiler]Time is always against us[/spoiler]

johnsi
Starting Member

10 Posts

Posted - 2008-01-15 : 02:57:56

yes..
My query is,
select distinct cardcode from acrd
It ll return cardcode values . And now i have to get this cardcode one by one and have to get the details of cardcode.

johnsi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-15 : 03:08:07

[code]DECLARE @sample TABLE
(
row_no int identity(1,1),
Field1 int,
Field2 int,
Field3 int,
Field4 int
)
INSERT INTO @sample (Field1, Field2, Field3, Field4)
SELECT 1, 2, 1, 2 UNION ALL
SELECT 1, 1, 3, 4 UNION ALL
SELECT 2, 1, 3, 4 UNION ALL
SELECT 3, 1, 4, 3

SELECT set_no = (row_no + 1)/2, field = 'Field1', val_1 = MIN(Field1), val_2 = MAX(Field1)
FROM @sample
GROUP BY (row_no + 1)/2 HAVING MIN(Field1) <> MAX(Field1)

UNION ALL

SELECT set_no = (row_no + 1)/2, field = 'Field2', val_1 = MIN(Field2), val_2 = MAX(Field2)
FROM @sample
GROUP BY (row_no + 1)/2 HAVING MIN(Field2) <> MAX(Field2)

UNION ALL

SELECT set_no = (row_no + 1)/2, field = 'Field3', val_1 = MIN(Field3), val_2 = MAX(Field3)
FROM @sample
GROUP BY (row_no + 1)/2 HAVING MIN(Field3) <> MAX(Field3)

UNION ALL

SELECT set_no = (row_no + 1)/2, field = 'Field4', val_1 = MIN(Field4), val_2 = MAX(Field4)
FROM @sample
GROUP BY (row_no + 1)/2 HAVING MIN(Field4) <> MAX(Field4)

ORDER BY set_no, field

/*
set_no field val_1 val_2
----------- ------ ----------- -----------
1 Field2 1 2
1 Field3 1 3
1 Field4 2 4
2 Field1 2 3
2 Field3 3 4
2 Field4 3 4

(6 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 02:08:22

quote:

Originally posted by khtan

DECLARE @sample TABLE
(
	Field1 	int,
	Field2	int, 
	Field3 	int,
	Field4	int
)
INSERT INTO @sample
SELECT	1, 2, 1, 2 UNION ALL
SELECT	1, 1, 3, 4

SELECT	field = 'Field1', MIN(Field1), MAX(Field1) FROM	@sample HAVING MIN(Field1) <> MAX(Field1)
UNION ALL
SELECT	field = 'Field2', MIN(Field2), MAX(Field2) FROM	@sample HAVING MIN(Field2) <> MAX(Field2)
UNION ALL
SELECT	field = 'Field3', MIN(Field3), MAX(Field3) FROM	@sample HAVING MIN(Field3) <> MAX(Field3)
UNION ALL
SELECT	field = 'Field4', MIN(Field4), MAX(Field4) FROM	@sample HAVING MIN(Field4) <> MAX(Field4)

KH
[spoiler]Time is always against us[/spoiler]

or

select field,min(field2),max(field2) from
(
select 'field2' as field,field2 from @sample
union all
select 'field3',field3 from @sample
union all
select 'field4',field4 from @sample
) as t
group by field

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources