Hi All,
I need to help with converting rows to columns in SQL2k.
Input:
Id Name Role
58Ron Doe Associate
58Mark BonasDoctor
59Mike JohnsonDoctor
59John SmithAssociate
102Chris CarterAssociate
102Ron Doe Associate
102James JonesAssociate
Output should look like:
IdDoctorAssoc1Assoc2Assoc3
58Mark BonasRon Doe NULLNULL
59Mike JohnsonJohn SmithNULLNULL
102NULLChris CarterRon Doe James Jones
There could be more than 3 associates in the input but I only need 3
above columns for associates.
I used following query:
SELECT Q.sales_id,
doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'
and Q2.sales_id = Q.sales_id),
assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id)
FROM view1 Q
GROUP BY sales_id
and I get this error "Subquery returned more than 1 value" since there
are multiple associate for Id 102.
Thenks<ambersaria420@.yahoo.com> wrote in message
news:1106185678.994241.297820@.c13g2000cwb.googlegr oups.com...
> Hi All,
> I need to help with converting rows to columns in SQL2k.
> Input:
> Id Name Role
> 58 Ron Doe Associate
> 58 Mark Bonas Doctor
> 59 Mike Johnson Doctor
> 59 John Smith Associate
> 102 Chris Carter Associate
> 102 Ron Doe Associate
> 102 James Jones Associate
>
> Output should look like:
> Id Doctor Assoc1 Assoc2 Assoc3
> 58 Mark Bonas Ron Doe NULL NULL
> 59 Mike Johnson John Smith NULL NULL
> 102 NULL Chris Carter Ron Doe James Jones
>
> There could be more than 3 associates in the input but I only need 3
> above columns for associates.
> I used following query:
> SELECT Q.sales_id,
> doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'
> and Q2.sales_id = Q.sales_id),
> assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role =
> 'associate' and Q2.sales_id = Q.sales_id),
> assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role =
> 'associate' and Q2.sales_id = Q.sales_id),
> assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role =
> 'associate' and Q2.sales_id = Q.sales_id)
> FROM view1 Q
> GROUP BY sales_id
> and I get this error "Subquery returned more than 1 value" since there
> are multiple associate for Id 102.
> Thenks
This is very awkward to write in TSQL, especially since the number of
associates may vary - you would need a cursor (maybe even nested cursors) to
loop through the table for each ID. A better solution is to do this in the
front end or using a reporting tool.
Simon|||I changed the query to following:
select distinct v1.sales_id, v1.name as doctor2 , v2.name as assoc1,
v3.name as assoc2, v4.name as assoc3
from
(select sales_id, max(case when role = 'Doctor' then name else NULL
end) as name from view1
group by sales_id) v1
left join (select sales_id , name from view1 where role = 'Associate'
) v2 on v1.sales_id = v2.sales_id
left join (select sales_id , name from view1 where role = 'Associate'
) v3 on v1.sales_id = v3.sales_id and (v3.name is null or v3.name >
v2.name)
left join (select sales_id , name from view1 where role = 'Associate'
) v4 on v1.sales_id = v4.sales_id and (v4.name is null or( v4.name >
v2.name and v4.name > v3.name))
However now it return extra row if there is more than one associate.
New Output:
58Mark BonasRon Doe NULL NULL
59Mike Johnsonjohn2 smithNULL NULL
102NULL Chris CarterJames JonesRon Doe
102NULL Chris CarterRon Doe NULL
102NULL James JonesRon Doe NULL
102NULL Ron Doe NULL
NULL
Thanks Again.|||It's ugly to write this in SQL. If you really want to do this, you can
do it using temp tables.
-------------------------------------
create table #T
(i int, name varchar(50), role varchar(50))
insert #T
values('58','Ron Doe','Associate')
insert #T
values('58','Mark Bonas','Doctor')
insert #T
values('59','Mike Johnson','Doctor')
insert #T
values('59','John Smith','Associate')
insert #T
values('102','Chris Carter','Associate')
insert #T
values('102','Ron Doe','Associate')
insert #T
values('102','James Jones','Associate')
-- doctor and first associate
select
i,
min(case when role='doctor' then name else 'zzz' end) as doctor,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T1
from #T
group by i
-- second associate
select
#T.i,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T2
from #T
left join #T1 on #T.i=#T1.i and #T.name=#T1.associate
where #T1.associate is null
group by #T.i
-- third associate
select
#T.i,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T3
from #T
left join #T1 on #T.i=#T1.i and #T.name=#T1.associate
left join #T2 on #T.i=#T2.i and #T.name=#T2.associate
where #T1.associate is null and #T2.associate is null
group by #T.i
-- output
select #T1.i, #T1.doctor, #T1.associate as assoc1, #T2.associate as
assoc2, #T3.associate as assoc3
from #T1
join #T2 on #T1.i=#T2.i
join #T3 on #T1.i=#T3.i
