Afternoon,
I have this data:
Block_Code TechCode Consumer_Flag MaxAdDn MaxAdUp Business_Flag CIRdn CIRup
500039702002023 10 1 8 1 0 0 0
500039702002023 10 0 0 0 1 32 4
500039702002023 20 0 0 0 1 1.568 1.568
500039702002023 50 0 0 0 1 100 100
and the query:
SELECT
CENSUSBLOCK_CENSUS2010,
tech ,
(select
case when accounttype = '2' then '0' else '1' end + ' , ' +
case when accounttype = '2' then '0' else CAST (MAX(download) AS VARCHAR(10)) end + ' , ' +
case when accounttype = '2' then '0' else CAST (MAX(upload) AS VARCHAR(10)) end + ' , ' +
case when accounttype = '2' then '1' else '0' end +' , ' +
case when accounttype = '2' then CAST (MAX(download) AS VARCHAR(10)) else '0' end + ' , ' +
case when accounttype = '2' then CAST (MAX(upload) AS VARCHAR(10)) else '0' end
from #tmp t2
where t2.CENSUSBLOCK_CENSUS2010 = t1.CENSUSBLOCK_CENSUS2010
and t2.tech = t1.tech
group by t2.accounttype
FOR XML PATH('')) [FCCData]
FROM #tmp t1
where CENSUSBLOCK_CENSUS2010 = '500039702002023'
group by CENSUSBLOCK_CENSUS2010,tech
ORDER BY 1</code>
which kind of does what I want but instead of 6 records I get 12 if there are two rows. So for the two rows with tech = 10 I get 12 records in the XML path.
like this:
CENSUSBLOCK_ tech FCCData
500039702002023 NULL NULL
500039702002023 10 1 , 8 , 1 , 0 , 0 , 00 , 0 , 0 , 1 , 32 , 4
500039702002023 20 0 , 0 , 0 , 1 , 1.568 , 1.568
500039702002023 50 0 , 0 , 0 , 1 , 100 , 100
Any thoughts would be great. I'm sure its something simple but I can't seem to get it.
Thanks
Laura