Select 1 row per order, but include order_line table?

Question

I have two tables

order [ order_id ]
order_line [ order_id, product_id, cat_id ]

I want to select every order & associated order_line, but I want row per order, and i only want to select cat_id from order_line

so i want a result like this..

results:
[0] order_id, cat_id1 , cat_id2, cat_id3
[1] order_id, cat_id

Possible?

Answer 1

this is based on the answer to my own question

CREATE FUNCTION [dbo].[GetCateogriesForOrder]
(
    @orderID int
)
RETURNS varchar(max)
AS
BEGIN
    declare @output varchar(max)
    select @output = COALESCE(@output + ', ', '') + cat_id
    from order_line
    where order_id = @orderID

    return @output
END

GO

SELECT order_ID, dbo. GetCateogriesForOrder(order_ID)
FROM order 

GO

Answer 2

Try this example:

if object_id('tempdb..#student') is not null drop table #student
create table #student
(
sid int
,sname varchar(10)
,lastname varchar(10)
,age int
)
insert #student select 1,'Joe','Block',20
union all select 2,'Adam','Brad',30
union all select 3,'Kevin','Campbell',33
union all select 4,'Mike','Gust',40
SELECT * FROM #student
if object_id('tempdb..#course') is not null drop table #course
create table #course
(
cid int
,sid int
,coursename varchar(15)
,duration int
,courselanguage varchar(10),
pos varchar(15)
)
insert #course select 1,1,'Maths',2,'English',2
union all select 2,1,'Science4',1,'Spanish4',1
union all select 2,1,'Science',1,'Spanish',3
union all select 3,1,'Geography',1,'Dutch',4
union all select 4,2,'Maths',2,'Dutch',2
union all select 5,2,'Science',2,'English',4
union all select 5,4,'test',545,'testlang',2
SELECT * FROM #course

declare @maxcourses int
select @maxcourses=(select max(numcourses)
from (select numcourses=count(*)
from #course
group by sid) q)

declare @pivotlist varchar(max)
,@pivotselectlist varchar(max)
select @pivotlist=
coalesce(@pivotlist+',','')
+'Sub'+ix+','
+'Dur'+ix+','
+'Lang'+ix
,@pivotselectlist=
coalesce(@pivotselectlist+',','')
+'Sub'+ix+'=coalesce(Sub'+ix+','''')'+','
+'Dur'+ix+'=coalesce(cast(Dur'+ix+' as int),0)'+','
+'Lang'+ix+'=coalesce(Lang'+ix+','''')'
from (select ix=convert(varchar(3),Number)
from master..spt_values
where Type='P' and Number between 1 and @maxcourses) q

declare @sql nvarchar(max)
set @sql='
;with Rnums as
(
select sid
,coursename
,duration
,courselanguage
,ix=pos
from #course
)
,PivotInput as
(
select sid
,AttribName=case ColNo
when 1 then ''Sub''+ix
when 2 then ''Dur''+ix
when 3 then ''Lang''+ix
end
,AttribVal=case ColNo
when 1 then coursename
when 2 then cast(duration as varchar(10))
when 3 then courselanguage
end
from Rnums
cross join (select 1 union all
select 2 union all
select 3) Cols(ColNo)
)
,PivotCourses as
(
select sid,'+@pivotlist+'
from PivotInput
pivot (max(AttribVal) for AttribName
in ('+@pivotlist+')
) PivotOutput
)
select s.sid
,sname
,lastname
,age
,'+@pivotselectlist+'
from #student s
join PivotCourses c on s.sid=c.sid'

--print @sql
exec sp_executesql @sql

Answer 3

Try this

declare @maxOrderCat int
select @maxOrderCat=(select max(numOrderCat)
from (select numOrderCat=count(*)
from order_line
group by order_id) q)

declare @pivotlist varchar(max)
,@pivotselectlist varchar(max)
select @pivotlist=
coalesce(@pivotlist+',','')
+'cat_id'+ix
,@pivotselectlist=
coalesce(@pivotselectlist+',','')
+'cat_id'+ix+'=coalesce(cat_id'+ix+','''')'
from (select ix=convert(varchar(3),Number)
from master..spt_values
where Type='P' and Number between 1 and @maxOrderCat) q
--Select @pivotselectlist
declare @sql nvarchar(max)
set @sql='
;with Rnums as
(
select order_id
,cat_id
,ix=convert(varchar,position)
from order_line
)
,PivotInput as
(
select order_id
,AttribName=case ColNo
when 1 then ''cat_id''+ix
end
,AttribVal=case ColNo
when 1 then cat_id
end
from Rnums
cross join (select 1 
) Cols(ColNo)
)
,PivotOrderCat as
(
select order_id,'+@pivotlist+'
from PivotInput
pivot (max(AttribVal) for AttribName
in ('+@pivotlist+')
) PivotOutput
)
select s.order_id
,'+@pivotselectlist+'
from Orders s
join PivotOrderCat c on s.order_id=c.order_id'

exec sp_executesql @sql