SQLTeam.com | Weblogs | Forums

Trying to XML path and its not grouping right


#1

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


#2

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


#3

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

#4

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


#5

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?


#6

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?


#7

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

#8

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>

#9

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.