sql server 2008 - Best method in writing this ColdFusion based report -

i'm looking see best way in writing report i'm developing in coldfusion 10 be.

basically, consists of reading data 2 tables in mssql database, applying conditions based on finds on specific column, populating data excel , sending out via email. i've written reports before it's simple reading data table, using poiutility create spreadsheet , sending via email, 1 little different because of conditions. i've read using cfspreadsheet instead of poiutility, i'm not sure in case.

here’s example of tables layout looks easy can put it:

table1 id | name | address  table2  id | appsinfo 

in excel report, i’ll have 1 sheet data laid out similar following:

id    | name  | address | appalpha | appbravo | appdelta |  12345 | john  | 123 ave | yes      | yes      | no       |  

my problem is, appsinfo column on table2 contains xml formatting each id :

<start> <id=”12345”> <appname=”appdelta”> <appname=”appbravo” </id> </start> 

in each row on excel sheet, data written out such if appsinfo column specific id contains app, list yes on corresponding row, if not contain app, no.

so going layout example above, final format of excel display way:

id    | name  | address | appalpha | appbravo | appdelta | 12345 | john  | 123 ave | no       | yes      | yes      | 

and on , on each id….

what best way in developing if contains specific apps on table2 appsinfo column, write yes , if not, write no each id on corresponding row?

leigh beat me answer, had similar solution using xml abilities of sql. xml can pain work with, sql server has syntax working xml.

select t1.id     , t1.name     , t1.address     --, t2.appsinfo     , case when t2.appsinfo.exist('//start/id/appname[@value="appalpha"]') = 1 'yes' else 'no' end appalpha     , case when t2.appsinfo.exist('//start/id/appname[@value="appbravo"]') = 1 'yes' else 'no' end appbravo     , case when t2.appsinfo.exist('//start/id/appname[@value="appdelta"]') = 1 'yes' else 'no' end appdelta #table1 t1 inner join #table2 t2 on t1.id = t2.id 

my setup was:

create table #table1 ( id int, name varchar(100), address varchar(200) ) create table #table2 ( id int, appsinfo xml )  insert #table1 (id, name, address) select 1, 'john', '123 sesame st' union select 2, 'jim', '42 douglas ln' union select 3, 'jack', '1 elm st' union select 4, 'joe', '21 jump st'  insert #table2 (id, appsinfo) select 1, '<start><id value="1"><appname value="appdelta"/><appname value="appbravo"/></id></start>' union select 2, '<start><id value="2"><appname value="appalpha"/><appname value="appdelta"/><appname value="appbravo"/></id></start>' union  select 3, '<start><id value="3"><appname value="appbravo"/></id></start>' union  select 4, '<start><id value="4"><appname/></id></start>' 

i had modify xml make valid. real difference between solution , leigh's i'm using sql cases output yes/no instead of doing in excel. either way work well.

the structure of actual xml using have effect on these queries, though. able put sample block of here? , version of sql server using? xml syntax sql 2000 bit different sql 2005+ , there's not xml datatype in sql 2000.

to build spreadsheet query, ray camden has article. , there's adobe docs cfspreadsheet.