SQL Query to join a column on a row
-
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...
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
-
It is hard for me to understand your question. My guess is:
SELECT veh.*, config.* FROM veh, config WHERE veh.make = config.make
-
the config table doesnt have a column named make, the make is a config record on the table. I got this solution to work (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81585) but I will need to join 80+ records and I worry about performance.
-
You should change the table design if possible - it's got some problems as you can probably tell from having to write this query.
-
The design is for mapping tables that I have no control over. The config table will do the mapping for me, its a bit of a hack I know. Im building a data entry form by using dynamically rendered userconrols by reading the config table. fun fun!
-
I agree with sam. This table design needs some help. If you really must do what you're suggesting here, this is the only way I can think of, and I'm still not sure it'll be what you're looking for...
SELECT config.id
, config.name
, config.type
, case
when config.name = 'make' then make
when config.name = 'model' then model
when config.name = 'color' then color
END AS value
FROM config config
INNER JOIN
( SELECT id
, make
, model
, color
FROM veh
) veh
ON veh.id = 1
-
heres what Ive got working good so far...
declare @config table
(
config_id int
,config_name varchar(50)
,config_req int
,config_length varchar(50)
,ctrl_id int
,map_fieldvarchar(50)
,map_dtype varchar(50)
,visible_add int
,min_val int
,max_val int
,is_money int
,mask varchar(50)
)
insert into @config
SELECT
mc.config_id
,mc.config_name
,mc.config_req
,mc.config_length
,mc.ctrl_id
,mc.map_field
,mc.map_dtype
,mc.visible_add
,me.min_val
,me.max_val
,me.is_money
,me.mask
FROM crm_map_config AS mc
LEFT JOIN CRM_MAP_MASKEDEDIT me on me.config_id = mc.config_id
declare @veh table
(
untidvarchar(8)
,vin varchar(17)
,make varchar(15)
,model varchar(15)
,modelnbr varchar(10)
,modelyear varchar(4)
,series varchar(10)
,bodystyle varchar(10)
,transmission varchar(10)
,cylinders varchar(2)
,hrsengine varchar(10)
,extcolorcode varchar(20)
,AmtOurPrice varchar(20)
)
insert into @veh
select
untid
,vin
,make
,model
,modelnbr
,modelyear
,series
,bodystyle
,transmission
,cylinders
,hrsengine
,extcolorcode
,AmtOurPrice
from vhvin where untid = '0A0018'
select
c.config_id
,c.config_name
,c.config_req
,c.config_length
,c.ctrl_id
,c.map_field
,c.map_dtype
,c.visible_add
,c.min_val
,c.max_val
,c.is_money
,c.mask
,[default_val] = case
-- when c.map_field = '' then v.
when c.map_field = 'untid' then v.untid
when c.map_field = 'vin' then v.vin
when c.map_field = 'make' then v.make
when c.map_field = 'model' then v.model
when c.map_field = 'modelnbr' then v.modelnbr
when c.map_field = 'modelyear' then v.modelyear
when c.map_field = 'series' then v.series
when c.map_field = 'bodystyle' then v.bodystyle
when c.map_field = 'transmission' then v.transmission
when c.map_field = 'cylinders' then v.cylinders
when c.map_field = 'hrsengine' then v.hrsengine
when c.map_field = 'extcolorcode' then v.extcolorcode
when c.map_field = 'AmtOurPrice' then v.AmtOurPrice
end
from @veh v
cross join @config c
-
You can use the unpivot operator in SQL Server 2005 to do this:
declare @config table (id int, name varchar(10), type varchar(10))
declare @vehicle table (id int, make varchar(10), model varchar(10), color varchar(10))
insert into @config values (1, 'make', 'varchar')
insert into @config values (2, 'model', 'varchar')
insert into @config values (3, 'color', 'varchar')
insert into @vehicle values (1, 'chevy', 's10', 'white')
insert into @vehicle values (2, 'ford', 'ranger', 'silver')
insert into @vehicle values (2, 'chevy', 'blazer', 'brown')
select c.id, c.name, c.type, iv.data_val
from @config c
inner join
(
select id, config_name, data_val
from (select * from @vehicle where id = 1) p
unpivot
(data_val for config_name in (make, model, color)) as unpvt) as IV
on IV.config_name = c.name
--Output:
id name type data_val
----------- ---------- ---------- ----------
1 make varchar chevy
2 model varchar s10
3 color varchar white
If you are using SQL Server 2000, then you would need to do the pivoting using UNION ALL operator and do it.
-
Joe C, Im not totally sure about everything you are talking about, but again I have no control over how the data structure is built, I only have control over the mapping/config table. As far as the vin number validation goes, I deal with large trucks and the vins are a different format that a regular car. Thats the least of my concerns anyway
Thanks
Ryan
-
One way
SELECT c.[id],c.[name],c.[type],v.[value]
FROM (SELECT [id], 'make' AS [name], [make] AS [value] FROM @veh UNION
SELECT [id], 'model', [model] FROM @veh UNION
SELECT [id], 'color', FROM @veh) v
LEFT JOIN @config c ON c.[name] = v.[name]
WHERE v.[id] = 1
ORDER BY c.[id] ASC
and putting the sub query in a view would make maintenance easier
Far away is close at hand in the images of elsewhere.
Anon. -
You might also want ot learn how to use OSO-11179 naming rules Yep... especially when it comes to ISO standards where you won't actually find the Mongolian goddess of marriage
--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)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply