Clicky

Have two tables, job order skill table with job order id and skill id, and skill table with skill id and skill name. The job order skill table will have a maximum of 4 skill ids for a job order id, but could have less. I am trying to have the query display the results as one row with job order id, skill id 1, skill name 1, skill id 2, skill name 2, and so on instead of multiples rows for each job order. Is this possible with a sql query?

Thanks

asked 12/14/2011 07:43

TSG954's gravatar image

TSG954 ♦♦


4 Answers:
Not really...
You would need a select statement with a variable number of columns. Number of columns would variate with the number of skills attached to the job order
link

answered

calinutz's gravatar image

calinutz

Use this
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
declare @jobs table(id integer, orderdate datetime)
declare @joborderskill table(orderid int, skillid int)
declare @skill table(skillid int, skillname varchar(20))

insert into @jobs
select 1, GETDATE()
union all select 2, GETDATE()
union all select 3, GETDATE()
union all select 4, GETDATE()

insert into @joborderskill
select 1, 1
union all select 1, 2
union all select 1, 3
union all select 2, 1
union all select 3, 2
union all select 4, 1
union all select 4, 3

insert into @skill
select 1, 'Mason'
union all select 2, 'Plumber'
union all select 3, 'Capenter'

SELECT  id, 
		Stuff((Select  ',' + skillname
                From @joborderskill T2
                inner join @skill T3 on T2.skillid = T3.skillid 
                Where   T1.id = T2.orderid
                For xml Path(''))
		, 1, 1, '') as skills 
From @jobs  T1
group by id
link

answered 2011-12-14 at 16:05:35

ewangoya's gravatar image

ewangoya

ewangoya, that is close but we are looking at breaking the names into seperate columns, not concatenating them into a single comma delimited list in one column. What about using a stored procedure to create a new table? Thanks for any ideas.
link

answered 2011-12-14 at 16:52:25

TSG954's gravatar image

TSG954

>> The job order skill table will have a maximum of 4 skill ids for a job order <<

try this

1:
2:
3:
4:
5:
6:
7:
select orderid, [1], [2], [3], [4]
from (
	select a.orderid, b.skillname, row_number() over (partition by a.orderid order by a.skillid) rn
	from job_order_skill a
	inner join skill b on a.skillid = b.skillid
) o
pivot (max(skillname) for rn in ([1], [2], [3], [4])) p
link

answered 2011-12-15 at 08:41:40

ralmada's gravatar image

ralmada

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×132

Asked: 12/14/2011 07:43

Seen: 225 times

Last updated: 12/15/2011 03:00