Combining rows in same table

Question

Combining rows in same table

Bill Kana

SSC Rookie

Points: 49
More actions
October 4, 2006 at 5:54 am

#169964

Hi all,
If this is not the correct forum, please advise.
How do I combine 2 rows of data in the same table to output one line of data? I have a table that has the company name, address, city, state on one row and the companies booth number and booth size on another row. I need to be able to extract this data (for Reporting Services) to be on one line. I have been reading about inner joins, but not sure if that is the right avenue of approach. Both rows in the table do have a common identifier - being the company name as well as an invoiceid number.
Thank you,
B

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

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Bill,

The design of the table you described doesn't sound right. Strictly speaking, each row in a table should uniquely identify the entity it is trying to model. In other words, each row in this table should uniquely identify a company. Storing company information across two rows seems to go against the whole principle behind relational modelling.

The end result is that writing queries to get the data you need becomes complex and, more importantly, will negatively affect performance.

I would suggest that you instead merge the company information into a single row and insert this into a new table.

You'll need to be able to identify the rows with address details and insert these rows into the table first.

Something like:

insert
select
from
where BootNumber is null

--update new table with company's booth details
update
set

from

newTable n

join

oldTable o

on o.CompanyName = n.CompanyName

where BoothNumber is not null

The update statement assumes that the rows that do not contain booth details have null values in the BoothNumber field.

Once the data is in one table you only need to worry about selecting the data.

Hope that helps,

Karl
source control for SQL Server

Lowell SSC Guru Points: 323518 More actions · Answer 2

without knowing the table structure for sure, something like the sql below is what you need. an inner join shows only matches...so if a company in table1 did not have a booth in table2, the company will not appear.

to see the differences in the joins, you can change "INNER JOIN" to any of the following:

LEFT OUTER JOIN (same as LEFT JOIN)

RIGHT OUTER JOIN (same as RIGHT JOIN)

FULL OUTER JOIN(same as FULL JOIN)

SELECT

TABLE1.INVOICE_NO,

TABLE1.COMPANY_NAME,

TABLE1.ADDRESS,

TABLE1.CITY,

TABLE1.STATE

TABLE2.INVOICE_NO AS INVOICE_NO2

TABLE2.BOOTH_NUMBER

TABLE2.BOOTH_SIZE

FROM TABLE1

INNER JOIN TABLE2 ON TABLE1.INVOICE_NO=TABLE2.INVOICE_NO

--alternatively: INNER JOIN TABLE2 ON TABLE1.COMPANY_NAME=TABLE2.COMPANY_NAME

ORDER BY TABLE1.COMPANY_NAME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bill Kana SSC Rookie Points: 49 More actions · Answer 3

Thanks for the input,

And maybe I'm missing something here but in your response you mentioned 2 tables, where I am only working with one table with the information that I need on more than 1 row. Can you elaborate more? Can I use an inner join on one table?

thx

Bill Kana SSC Rookie Points: 49 More actions · Answer 4

Karl,

I think I understand the above statement, I'm assuming where you have (<Columns&gt - that is where I would put all of the columns I want from that table? Also, what do you mean by o.boothnumber and newtable n. I only know SQL on an entry level, so forgive me.

thx

Lowell SSC Guru Points: 323518 More actions · Answer 5

to really help, we'll need the real schema; use enterprise manager, select the table, right click>>All Tasks>>Generate SQL Scripts

paste the results here;

you can join the same table on itself when you have the situation you describe, but anything meaningful requires the table design

worthless example:

SELECT

TABLE1.INVOICE_NO,

TABLE1.COMPANY_NAME,

TABLE1.ADDRESS,

TABLE1.CITY,

TABLE1.STATE

TABLE2.INVOICE_NO AS INVOICE_NO2

TABLE2.BOOTH_NUMBER

TABLE2.BOOTH_SIZE

FROM TABLE1

INNER JOIN TABLE1 AS TABLE2 ON TABLE1.INVOICE_NO=TABLE2.INVOICE_NO and TABLE1.SOMECOL is null and table2.somecol is not null

--alternatively: INNER JOIN TABLE2 ON TABLE1.COMPANY_NAME=TABLE2.COMPANY_NAME

ORDER BY TABLE1.COMPANY_NAME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLZ SSChampion Points: 12872 More actions · Answer 6

Hi Bill,

If you notice, in the FROM and JOIN part of the statement, immediately after referencing the table names I placed an "n" and an "o" respectively. These are optional aliases that reference the tables newTable and oldTable respectively and are just intended to shorten the amount of typing you have to do.

So, where you see o.BoothNumber, this means get the value of BoothNumber from the oldTable. If I hadn't used an alias I would have had to write oldTable.BoothNumber.

The JOIN part of the statement also includes the ON statement. The ON statement is basically saying, "find all matching rows between these two tables using the CompanyName to identify a match".

The WHERE clause ensures that you only select rows from the oldTable that actually have a BoothNumber. Note that I'd made a mistake in my original post. I should have qualified the BoothNumber in the WHERE clause with o, which I have now done.

Hope that helps.

--update new table with company's booth details

update

newtable

set

BoothNumber = o.BoothNumber,

BoothSize = o.BoothSize

from

newTable n

join

oldTable o

on o.CompanyName = n.CompanyName

where o.BoothNumber is not null

Karl
source control for SQL Server

Dan Murray Right there with Babe Points: 779 More actions · Answer 7

I agree that the table isn't in good shape, but in the real world 'Stuff happens'. Assuming that there is a correct common key that you can link the partial rows on then the situation with the table you're communicating seems like this.

Row1: Key| D | null | T | null

Row2: Key| null | A | null | A

and you want the query to return:

Key|D|A|T|A, then this would do the trick. Good Hunting

SELECT DISTINCT tb1.d,tb2.a,tb1.t,tb2.a2

FROM

TblOne tB1, TbLOne TB2

WHERE

TB1

.[Key]=TB2.[Key]

and

not

TB1.D is null

and

not

Tb2.a is null

Bill Kana SSC Rookie Points: 49 More actions · Answer 8

Thanks to all of you for being patient and helping me out. Although I haven't had a chance to try and of your suggestions, I appreciate all the responses....stay tuned.