Question on inserting a single row multiple times based on its value

[chrisdoesstuff]

SQL express 2008

My data contains a set of values that I'd like to use parts of in a query. (this isn't real data I'm just trying to fully grasp the concept)

Ex animals.value and the values are 'Rhino', 'Leopard', 'Cheetah', 'Monkey'

I want to insert the data into a table variable but only if it's cheetah or monkey and I want to be able to select cheetah as ground and monkey as tree in my final query.

I started to use
Insert into @zooanimals SELECT value, location from animals where animals.value = 'cheetah" or animals.value = 'monkey'


However that doesn't work as I only end up with one row. Should I be selecting it differently in my final query? What's the easiest way to accomplish this?


My final query will be like

SELECT fac.code, fac.location ani.value, ani.location
from Facilities fac
Left outer join animals ani
on fac.location = ani.location

should give me all the facilities and show me which ones have animals that are cheetah or monkey and in the case of cheetah and monkey being in the same location it can show both in separate columns.

Any help you can provide is appreciated

 

[simian336]

So how many rows does this produce by itself?

SELECT value, location from animals where animals.value = 'cheetah" or animals.value = 'monkey'

Simi

 

[chrisdoesstuff]

One or two depending on the data. It returns the values so if cheetah is there then it returns it and if monkey is present it returns that too. So it returns the location twice and the value once per animal.

 

[simian336]

Sounds correct... Not sure I am following. Perhaps you should show us some data and then your expected results from that data.

Simi

 

[chrisdoesstuff]

What I want is to be able to select monkeys as column1 and cheetah as column2 so that I get one row per location but two potential values.

I'd like to insert it into my table variable but I'm ok with querying the table twice to get the values. I was hoping that there is an easy way to insert the data into the table variable.

 

[Qik3Coder]

Chris,
This is known as a CROSS TAB.
You want to take vertical data and turn it on it's side.
faq183-5269

You need to "massage" your data into a CROSS-TAB-able format.

The easiest thing to do is use a derived query with ROW_Number() function that partitions based on location.

Base data:
Table 1 - LocationCode, Location
Table 2 - AnimalLocation, Animal

Intermediate Data:
LocationCode, Location, Animal, AnimalNumber

Final Data:
LocationCode, Location, Animal1, Animal2, Animal3,...


You will need a "CASE statement" to turn the data on it's side. This will look like

..., Case when AnimalNum = 1 then Animal Else '' END, ...

HTH,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[simian336]

I see where you are going now....

Since you appear to be trying to learn. You might consider that in your small test environment that you will be fine, but if you start playing with thousands of rows then you will be in trouble.

Most of the time in databases vertical data is your friend.

Simi

 

[Qik3Coder]

DECLARE @_SQL varchar(8000)
SELECT @_SQL = 'SELECT f.FacilityID, f.FacilityName'
SELECT  @_SQL = @_SQL + ', MAX(CASE WHEN ani.AnimalName = ''' + a.AnimalName + ''' THEN ani.Number else 0 END) as ['+a.AnimalName+']' +char(13)
	FROM (Select DISTINCT AnimalName FROM #Animals) as a
SELECT @_SQL = @_SQL + 'FROM #Facilities f LEFT JOIN #Animals ani ON f.facilityID = ani.facilityID GROUP BY f.FacilityID, f.FacilityName'
--PRINT @_SQL
Exec(@_SQL)

Have fun with that,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.