Trying to XML path and its not grouping right

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

I see one row for tech=10 in your output, not 12

I don't know if this is still relevant, I had written this a while ago and forgot to click Save

Without the data that feeds your query I don't have a lot of insight into the problem, so this is a guess:

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 (SELECT DISTINCT CENSUSBLOCK_CENSUS2010,tech FROM #tmp ) t1
where CENSUSBLOCK_CENSUS2010 = '500039702002023'
ORDER BY 1

Thanks James. same story different chapter. Heres the data:

create table TempData(
Block_Code float,
TechCode float,
Consumer_Flag float,
MaxAdDn float,
MaxAdUp    float,
Business_Flag float,
CIRdn float,
CIRup float) 

go 
GO
INSERT INTO TempData VALUES ( 500039702002023, 10, 1,8,1,0,0,0 )
INSERT INTO TempData VALUES ( 500039702002023, 10, 0,0,0,1,32,4 )
INSERT INTO TempData VALUES ( 500039702002023, 20, 0,0,0,1,1.568,1.568 )
INSERT INTO TempData VALUES ( 500039702002023, 50, 0,0,0,1,100,100 )

the query isnt supplying the same data as before but you can see it will still give you extra numbers which I dont want.

SELECT
    Block_Code,
    techCode ,
        (select  
                        case when Consumer_Flag = '1' then '1' else '0' end + ' , ' + 
                        case when Consumer_Flag = '0' then '0' else CAST (max(CIRdn) AS VARCHAR(10)) end + ' , ' + 
                        case when Consumer_Flag = '0' then '0' else CAST (max(CIRup) AS VARCHAR(10)) end + ' , ' + 
                        case when Consumer_Flag = '1' then '1' else '0' end +' , ' + 
                        case when Consumer_Flag = '0' then CAST (max(CIRdn) AS VARCHAR(10)) else '0' end + ' , ' + 
                        case when Consumer_Flag = '0' then CAST (max(CIRup) AS VARCHAR(10)) else '0' end
         from TempData t2
         where t2.Block_Code = t1.Block_Code
         and t2.techCode = t1.techCode
         group by t2.Consumer_Flag
         FOR XML PATH('')) [FCCData]         
FROM (SELECT DISTINCT Block_Code,techCode FROM TempData ) t1
group by Block_Code,techCode
ORDER BY 1

Any thoughts would be most appreciated.

Laura

Its not clear what your desired output is. Would you please post what you want to see from your query using the sample input data?

I want it to show up like this:

500039702002023.00000 , 10 , 1 , 8 , 1 ,  1 , 32 , 4

but it shows up like this:

500039702002023.00000 , 10 , 1 , 8 , 1 , 0 , 0 , 00 , 0 , 0 , 1 , 32 , 4

Does that help?

Well sure it shows up like that! Look at your select statement. You are concatenating all those extra zeros with your case statements.

Maybe you want something like this:

case when Consumer_Flag = '1' then '1' + ' , ' else '' end + 
case when Consumer_Flag = '0' then '' else CAST (max(CIRdn) AS VARCHAR(10)) + ' , ' end +
case when Consumer_Flag = '0' then '' else CAST (max(CIRup) AS VARCHAR(10)) + ' , ' end  + 
case when Consumer_Flag = '1' then '1' +' , ' else '' end  + 
case when Consumer_Flag = '0' then CAST (max(CIRdn) AS VARCHAR(10)) + ' , ' else '' end  + 
case when Consumer_Flag = '0' then CAST (max(CIRup) AS VARCHAR(10)) else '' end

Maybe this would help? I cahnged it to xml auto so you can see. It shows two rows for techcode of 10 and one row for everything else. How do I make the two rows into one?

>     500039702002023.00000 , 10 , <CIRup>1 , 8 , 1 , 0 , 0 , 0</CIRup><CIRup>0 , 0 , 0 , 1 , 32 , 4</CIRup>
>     500039702002023.00000 , 20 , <CIRup>0 , 0 , 0 , 1 , 1.568 , 1.568</CIRup>
>     500039702002023.00000 , 50 , <CIRup>0 , 0 , 0 , 1 , 100 , 100</CIRup>

Got it. It was a grouping error. I had to put the data in a temp table and line everything up, then use the xml path.

Thanks everyone for their help.