Relational tables basics - 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 2000 Forums

SQL Server Development (2000)

Relational tables basics

Author

Topic

ds9
Starting Member

38 Posts

Posted - 2007-02-16 : 05:45:01

swatib
Posting Yak Master

173 Posts

Posted - 2007-02-16 : 07:16:31

Please be specific. Your column name "ParameterA" in first table is value of a column in second table.

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 07:38:37

what is the relationship of these 2 table ? What is the expected result from the sample data provided ?

ds9
Starting Member

38 Posts

Posted - 2007-02-16 : 09:53:23

Hi

That's it swatib! The column names in the first table are column values in the second table. And I want to build a view tha basically adds the Paramter description to the parameter value like this:

Product | ParameterA | Parameter description
XPTO 5 blábláblá

thanks
ds9

swatib
Posting Yak Master

173 Posts

Posted - 2007-03-01 : 01:56:47

Could you please provide better sample input and output. What is the role of ParametrB and ParameterC?

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 02:08:24

With insufficient description / details on your requirement, poor sample data and unmatched result . . .

I think this is what you want


declare @atable table
(
	Product		varchar(10),
	ParameterA	varchar(10),
	ParameterB	varchar(10),
	ParameterC	varchar(10)
)
insert into @atable 
select	'XPTO', '5',  '10', 'good'	union all
select	'XYZX', '29', '22', 'good'

declare @anothertable table
(
	[Parameter Code]		int,
	[Parameter Name] 		varchar(10),
	[Parameter description]		varchar(12)
)

insert into @anothertable
select	1, 'ParameterA', 'blábláblá'	union all
select	2, 'ParameterB', 'blábláblá'	union all
select	3, 'ParameterC', 'bláblábla'	union all
select	4, 'good',	 'good blá'	union all
select	5, '5',		 '5 blá'	union all
select	6, '10',	 '10 bláblá'

select	a.Product, b.[Parameter Name], b.[Parameter description]
from	@atable a inner join @anothertable b
	on	a.ParameterA	= b.[Parameter Name]
union all
select	a.Product, b.[Parameter Name], b.[Parameter description]
from	@atable a inner join @anothertable b
	on	a.ParameterB	= b.[Parameter Name]
union all
select	a.Product, b.[Parameter Name], b.[Parameter description]
from	@atable a inner join @anothertable b
	on	a.ParameterC	= b.[Parameter Name]

/*
Product    Parameter Name Parameter description 
---------- -------------- --------------------- 
XPTO       5              5 blá
XPTO       10             10 bláblá
XPTO       good           good blá
XYZX       good           good blá
*/

If this is not what you want, please re-read mine & swatib's post.

ds9
Starting Member

38 Posts

Posted - 2007-03-01 : 06:26:43

Hi

First of all thank you for your persistence. It is highly appreciated.

Let me try to rephrase my question. I have 2 tables. The only relation between the 2 tables is that the header of one equals the contents of one of the fields in the second table.
Here is another (hopefully better) example:

TABLE A
Product|AverageWeigth|AverageQty|TotalOutput
Apple|20|13493|239444
Strawberry|5|5000|43443
Orange|25|2324|235244

TABLE B
ParameterCode|ParameterName|ParameterDescription
1|AverageWeigth|Means the average weight in grams of each unit during one week
2|AverageQty|Means the average quantity produced per day during one week
3|TotalOutput|Means the total quantity produced during one week

The output I want is a kind of "copy paste transpose" of table A with some additional info from table B:

ParameterName|Apple|Strawberry|Orange|ParameterDescription
AverageWeigth|20|5|25|Means the average weight in grams of each unit during one week
AverageQty|13493|5000|2324|Means the average quantity produced per day during one week
TotalOutput|239444|43443|235244|Means the total quantity produced during one week

Again
Many thanks
ds9

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 07:11:22

[code]
declare @A table
(
Product varchar(10),
AverageWeigth int,
AverageQty int,
TotalOutput int
)
insert into @A
select 'Apple', 20, 13493, 239444 union all
select 'Strawberry', 5, 5000, 43443 union all
select 'Orange', 25, 2324, 235244

declare @B table
(
ParameterCode int,
ParameterName varchar(15),
ParameterDescription varchar(70)
)
insert into @B
select 1, 'AverageWeigth', 'Means the average weight in grams of each unit during one week' union all
select 2, 'AverageQty', 'Means the average quantity produced per day during one week' union all
select 3, 'TotalOutput', 'Means the total quantity produced during one week'

select b.ParameterName,
[Apple] = sum(case when a.Product = 'Apple' then
case b.ParameterName
when 'AverageWeigth' then a.AverageWeigth
when 'AverageQty' then a.AverageQty
when 'TotalOutput' then a.TotalOutput
else 0
end
else 0
end
),
[Strawberry] = sum(case when a.Product = 'Strawberry' then
case b.ParameterName
when 'AverageWeigth' then a.AverageWeigth
when 'AverageQty' then a.AverageQty
when 'TotalOutput' then a.TotalOutput
else 0
end
else 0
end
),
[Orange] = sum(case when a.Product = 'Orange' then
case b.ParameterName
when 'AverageWeigth' then a.AverageWeigth
when 'AverageQty' then a.AverageQty
when 'TotalOutput' then a.TotalOutput
else 0
end
else 0
end
),
b.ParameterDescription
from @A a cross join @B b
group by b.ParameterName, b.ParameterDescription
order by b.ParameterName

/*
ParameterName Apple Strawberry Orange ParameterDescription
--------------- ----------- ----------- ----------- ----------------------------------------------------------------------
AverageQty 13493 5000 2324 Means the average quantity produced per day during one week
AverageWeigth 20 5 25 Means the average weight in grams of each unit during one week
TotalOutput 239444 43443 235244 Means the total quantity produced during one week
*/
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources