SQL Query to join a column on a row

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)

SQL Query to join a column on a row

Author

Topic

ryanoc
Starting Member

25 Posts

Posted - 2007-04-03 : 09:51:34

config table
--------------------
id name type
--------------------
1 make varchar
2 model varchar
3 color varchar

veh table
--------------------------
id make model color
--------------------------
1 chevy s10 white
2 ford ranger silver
2 chevy blazer brown

recordset needed for veh.id=1
---------------------------
id name type value
---------------------------
1 make varchar chevy
2 model varchar s10
3 color varchar white

ryanoc
Starting Member

25 Posts

Posted - 2007-04-03 : 09:52:16

I have 2 tables, my vehicle data table and my config table. I need a query to join them by a datarow and a data column. Heres my tables...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 10:14:16

[code]SELECT c.ID,
c.Name,
c.Type,
x.Info AS Value
FROM Config AS c
INNER JOIN (
SELECT 1 AS C,
Make AS Info
FROM Veh
WHERE ID = 1

UNION ALL

SELECT 2,
Model
FROM Veh
WHERE ID = 1

UNION ALL

SELECT 3,
Color
FROM Veh
WHERE ID = 1
) AS x ON x.C = c.ID[/code]

Peter Larsson
Helsingborg, Sweden

ryanoc
Starting Member

25 Posts

Posted - 2007-04-03 : 10:28:19

I'm a little concerned about the performance since the real query will have almost 80 fields to do this on instead of the example table of only 3, but it works great. How concerned should I be do you think?

Thanks!
Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 10:30:24

I would be more concerned that table is not normalized.
This is the price you pay.

Try and see what response time you get!

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-03 : 11:03:46

[code]
declare @config table
(
[id] int,
[name] varchar(10),
type varchar(10)
)
insert into @config
select 1, 'make', 'varchar' union all
select 2, 'model', 'varchar' union all
select 3, 'color', 'varchar'

declare @veh table
(
[id] int,
make varchar(10),
model varchar(10),
color varchar(10)
)
insert into @veh
select 1, 'chevy', 's10', 'white' union all
select 2, 'ford', 'ranger', 'silver' union all
select 2, 'chevy', 'blazer', 'brown'

select c.id,
c.name,
c.type,
[value] = case when c.[name] = 'make' then v.make
when c.[name] = 'model' then v.model
when c.[name] = 'color' then v.color
end
from @veh v cross join @config c
where v.id = 1

/*
id name type value
----------- ---------- ---------- ----------
1 make varchar chevy
2 model varchar s10
3 color varchar white
*/
[/code]

ryanoc
Starting Member

25 Posts

Posted - 2007-04-03 : 11:10:34

ok, i got the first solution to work, but I also have a table with parameters on how the data is validated and I cant join it now. Heres my query. The part with the MASKEDEDIT table wont return values when I include the inner join.

SELECT
mc.config_id
,mc.config_name
,mc.config_req
,mc.config_length
,mc.ctrl_id
,mc.map_field
,mc.map_dtype
,mc.default_val
,mc.visible_add
,me.min_val
,me.max_val
,me.is_money
,me.mask
,x.Info AS def_val
FROM crm_map_config AS mc
LEFT JOIN MASKEDEDIT me on me.config_id = mc.config_id
INNER JOIN (

SELECT 1 AS C,
untid as info
FROM VHVIN
WHERE untid = '0A0018'

UNION ALL

SELECT 2,
vin
FROM VHVIN
WHERE untid = '0A0018'

UNION ALL

SELECT 4,
CAST ( AmtOurPrice AS varchar(50) )
--AmtOurPrice
FROM VHVIN
WHERE untid = '0A0018'

) AS x ON x.C = mc.config_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 11:11:56

Nice

SELECT		c.ID,
		c.Name,
		c.Type,
		CASE
			WHEN c.Name = 'make' THEN v.Make 
			WHEN c.Name = 'model' THEN v.Model
			WHEN c.Name = 'color' THEN v.Color
		END AS [Value]
FROM		@Config AS c
INNER JOIN	@Veh AS v ON v.ID = 1

Peter Larsson
Helsingborg, Sweden

ryanoc
Starting Member

25 Posts

Posted - 2007-04-03 : 11:43:33

Thanks guys, I got the reply from KH to work with all the joins. so far so good!

Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources