SQL Query to take multiple rows and display as one row with multiple columns

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
TSG954Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

calinutzCommented:
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
Ephraim WangoyaCommented:
Use this
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

Open in new window

TSG954Author Commented:
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.
ralmadaCommented:
>> The job order skill table will have a maximum of 4 skill ids for a job order <<

try this


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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.