Season Greetings,
I need query that can add skills from Table1 into Table2.
It looks impossible to me...
Lots of stuff looks impossible until you have the
tools to do it easily.You can actually write an Access sql
solution to create table2, which can be viewed as
a simple crosstab.But the sql is messy and complicated
and do you really need that?
.
The RAC (Relational Application Companion) utility was
built, among other things, to solve many types of problems
easily and with a minimum of sql coding.You can think of
it as a higher level language than sql.RAC is native to
Sql Server 2000 so all you need is any server version
including the MSDE 2000 version.Simply pipe your Access
data to the server or setup a linked server to Access
or use an Access ADP.
You will find RAC in many ways similar to the Access
crosstab but much more powerful with many more features
and options.RAC can be used in many ways from crosstabs
to solving hierachy problems.
Here's a simple RAC solution done entirely on the server.
Table2 could be obtained in 1 RAC execution but I'll
present the solution piecemeal so you can more readily see
what's being done.
create table #MI ([ID] int primary key,Code int,Skill varchar(25))
go
insert #MI values(1,3267,'Machine Operator')
insert #MI values(2,3267,'Warehouse Worker')
insert #MI values(3,3268,'Janitor')
insert #MI values(4,3269,'Assembly')
insert #MI values(5,3269,'Construction Eng.')
insert #MI values(6,3269,'General Labor')
insert #MI values(7,7269,'Janitor')
insert #MI values(8,3269,'Forklift: Gas')
insert #MI values(9,3269,'Machine Operator')
insert #MI values(10,3269,'Production')
insert #MI values(11,3269,'Warehouse Worker')
In the first RAC run the key is to create the
column 'CodeGrp'.It's a sort of rank/counter
that binds together same values of 'Code' based
on the sort order by ID.It will be used as a
GROUP BY column in the 2nd run of RAC.The result
of the 1st RAC run is saved in table ##MI2
in the @select parameter.
Exec Rac
-- @transform is like the Access crosstab Transform.
-- It can used for many things in RAC including
-- creating multiple aggregates for a crosstab.
-- Here were not creating a crosstab just a table
-- so we use the RAC keyword '_dummy_'.
@transform='_dummy_',
-- The @rows parameter is somewhat similar to the
-- Access crosstab GROUP BY statement.
@rows='Code & Skill & [ID]',
@rowsort='[ID]',-- Sort the columns in @rows by ID.
-- If we were creating a crosstab @pvtcol would have
-- the column to pivot just like the Access crosstab.
@pvtcol='Access Reports',
@from='#MI',
@defaults1='y',
-- @rowbreak can eliminate duplicate @rows columns
-- like Access Reports.Here we want all duplicate 'Codes'.
@rowbreak='n', -- 'n' is short for 'no'.
-- @rowindicators creates the CodeGrp counter of type integer.
-- (RAC can create many different types of counters with no
-- coding required).
@rowindicators='Code{CodeGrp}',@counterdatatype='int',
@select='Select _rows_,CodeGrp
into ##MI2
from rac
order by rd'
select * from ##MI2 order by 1*[ID]
Code Skill ID CodeGrp
------ ----------------- ---- -----------
3267 Machine Operator 1 1
3267 Warehouse Worker 2 1
3268 Janitor 3 2
3269 Assembly 4 3
3269 Construction Eng. 5 3
3269 General Labor 6 3
7269 Janitor 7 4
3269 Forklift: Gas 8 5
3269 Machine Operator 9 5
3269 Production 10 5
3269 Warehouse Worker 11 5
Look at the values of 'CodeGrp' and how they
relate to 'Code'.To create table2 we can simply
group by 'Codegrp' and bring along the corresponding
'Code'.We pivot 'Skill' as a rank based on Skills
sort order.In other words the 1st pivot column is
the 1st skill for a CodeGrp/Code combination (row).
The 2nd pivot column is the 2nd skill for the row etc.
Exec Rac
@transform='(Skill) as Skill',
@rows='CodeGrp & Code',
@pvtcol='Skill',-- Pivot the 'Skill' column.
@from='##MI2',
@defaults1='y',
-- The @rank option tells RAC to create the pivot columns
-- as a ranking of the pivot column 'Skill'.@rank='some characters'
-- prefixes the character string to the pivot column.So the
-- 1st pivot column is named 'Skill1'.The 2nd pivot column 'Skill2' etc.
-- The @ranklimit options sets up a maximum of 9 pivot columns,ie.
-- 'Skill1'-'Skill9 regardless if there's any data in a particular
-- pivot column.
@rank='Skill',@ranklimit='9',
-- Here the RAC Select statement is used to create ##table2.
-- The rd column is an internal RAC counter based on the
-- sort order of the rows.
@select='Select cast(rd as tinyint) as rd,Code,_pvtcols_
into ##table2
from rac
order by rd'
Here is ##table2 exactly as you posted it (I think
You can do much more.For example you can easily concatenate
all the pivot column to a single delimited string.There are many
possibilities.
select * from ##table2 order by rd
(Hope the wrapping doesn't create a mess
rd Code Skill1 Skill2 Skill3 Skill4
Skill5 Skill6 Skill7 Skill8
Skill9
---- ---- ----------------- ----------------- ----------------- ------------
----- ----------------- ----------------- ----------------- ----------------
- -----------------
1 3267 Machine Operator Warehouse Worker
2 3268 Janitor
3 3269 Assembly Construction Eng. General Labor
4 7269 Janitor
5 3269 Forklift: Gas Machine Operator Production Warehouse
Worker
And yes,RAC does come with a point and click GUI as an
alternative to batch execution.
For more info on RAC for S2k including documentation and
an evaluation version visit:
www.rac4sql.net
We also offer a free tool, QALite, for adminstrating
and querying MSDE.
Happy Holidays from RacTeam
