怎样将查询出的记录合并到一行 不重复

Question

怎样通过查询将下面的重复cardno 变成一行即

workshop cardno qty Inticket exitticket
T01 116680300169 2 4 20
T01 116680300170 2 4 20

---

select
total.Workshop,
total.Card# as cardno,COUNT(*)as qty,
case when total.Ticket#=4 then total.Ticket# end as Inticket,
case when total.Ticket#=20 then total.Ticket# end as exitticket
from(
select
only.Ticket#,
only.[Receive Time],
only.Workshop,
only.Card#
from test.dbo.TicketDetailData125 only
group by
only.Workshop,
only.Card#,
only.Ticket#,
only.[Receive Time]

)total
where
total.Workshop=total.Workshop
group by
total.Workshop,
total.Card#,
total.Ticket#,
total.[Receive Time]
order by
total.Workshop
现在查出的结果

Answer 1

select
total.Workshop,
total.Card# as cardno,COUNT(*)as qty,
MAX(case when total.Ticket#=4 then total.Ticket# END) as Inticket,
MAX(case when total.Ticket#=20 then total.Ticket# END) as exitticket
from(
select
only.Ticket#,
only.[Receive Time],
only.Workshop,
only.Card#
from test.dbo.TicketDetailData125 only
group by
only.Workshop,
only.Card#

)total
where
total.Workshop=total.Workshop
group by
total.Workshop,
total.Card#
order by
total.Workshop