SQL Query match rows in one table

Question

Post reply

SQL Query match rows in one table

shenyjoy

SSC-Addicted

Points: 421
More actions
May 15, 2008 at 7:06 am

#190606

In a table in SQl I have the same data in two rows that have a field in common that represent the same event 352. In the first row I have all the details displayed in columns. In the second row I want to display the same data preferably without changing anything in the database.

1 2 3 4 5 6 7 8

Id Year WeekDay FacultyId Module Code RoomId Code

352 AY0809 1 700014254 LNG320 52 MQ7117 20356

352 AY0809 1 MQ71B09 20356

So I have this table that is populated from other tables. You can see that columns 4,5,6 need to have the same data but in this table they do not show.

I am trying to write a query in SQL Server that helps me to check If the CODE is the same and RoomId or facultyid is different populate the columns 4,5,6. Is this possible.

When I trasfer these rows to another table that code checks against each field and the one with the blank row is not populated

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

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

Mahesh Bote

SSCrazy Eights

Points: 9107

May 16, 2008 at 8:25 am

#816525

[font="Verdana"]Why do you need such duplicate values into your table? This can be done easily, but let us know the exact stuff you wants to do on a table. Coz this is not a good idea to keep such duplicate values in a table.

Mahesh[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

I agree with Mahesh... this type of duplicate data may be a real problem.

However, if that's what you need, just have the final table do a "self update" using 2 table aliases for the same table... criteria for one would be where the columns are null and criteria for the other is where they are not. Join on the common column.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

shenyjoy SSC-Addicted Points: 421 More actions · Answer 3

Thanks Mahesh & Jeff

I dont want to have the duplicate rows in the table in my database. I was wondering if I could create a view tat will help me to show the duplicate values in both the rows.

Another database needs to upload data from this view to populate their view and their script checks for blanks and creates an error log if it finds blank values.

Scenario:

Oracle Db has a view called "SCHD".

SQL Server needs a view "TTSCHD" - In this view I need the rows to show duplicate values, so that the Oracle view can upload without errors.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I suppose... instead of doing an UPDATE using the method I suggested, it could be just a SELECT as part of the view.

The real question would be, what are you going to do if you have more than 1 row with non-null data?

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

shenyjoy SSC-Addicted Points: 421 More actions · Answer 5

When I use a SELECT statement with the JOIN, I get a cartesian join and it shows me all the columns reapeated. I tried using an UPDATE statement and it updates the fields which are NULL , by exchanging the values.

Is there a cursor or something that needs to be written to accomplish this, coz as u said I have more than one blank rows.

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 6

Mahesh Bote

SSCrazy Eights

Points: 9107

May 19, 2008 at 7:11 am

#817155

[font="Verdana"]Post your table structure with sample data and the stuff you wants to do, so that we can try o give you the perfect solution.

Mahesh[/font]

MH-09-AM-8694

shenyjoy SSC-Addicted Points: 421 More actions · Answer 7

In the sheet attatched I have an extract of the data from the database. I have only included the columns I require .

Columns 2 & 3 refer to a same code it is an event that happens on Thursday(Weekday 5), it is repeated on two rows because of one column which is the LecturerId, the 3 row identifies that there is an additional person teaching this event.

Columns 4& 5 refer to the same scenario , the only difference is that it happens on Sunday(Weekday 1).

Columns 6 & 7 refer to an event on Monday(Weekday 2), it has only one faculty teaching it, but there is an additional room this time, hence an additional row.

If you carefully look at the sheet the blanks highlighted in Yellow must have the same values as the row above it.This does not show in the database, maybe coz it is designed like that.

I DONT WANT TO MAKE ANY CHANGES IN THE DATABASE:

My only intention is to see every blank cell with the value so that it duplicates it, I thought this can be done by one of the following

1) Create a view

2) Use the update command.

I tried both the above but am not able to achieve this. Can u help me in coding this. I hope this is clear

Pat Reiter Old Hand Points: 378 More actions · Answer 8

The idea is to reuse the values in the first row of each set where there is a blank?

I can name that query in one statement, but easier to describe when separate in to steps

Step 1. identify slot-1 rows

Create view V_Slot1 as

select (list all the columns here)

from my_table

where SlotEntry = 1

Step 2. identify not-slot 1 rows

Create view V_Not1Raw as

select (list all the columns here)

from my_table

where SlotEntry > 1

Step 3. Fill-in the blanks in the raw not-1 view.

join each V_Not1Raw to its V_Slot1 sibling on all the not-yellow columns (except for SlotEntry)

To determine each payload column: if the 'raw' value is not null, use it. else use the Slot1 value.

Step 4. Union the view in Step 3 with the view in Step 1.