sql server 2008 r2 - SQL query to export single data row to multiple CSV rows -

sql server 2008 r2 - SQL query to export single data row to multiple CSV rows -

we trying automate steps , need setup query dump info on schedule. problem having taking single row of info sql table , dumping multiple rows of data. sql table used parts order form , has fields 10 parts entered (part01 - part10) , fields quantities of each part (qty01-qty10). in orders, not using 10 lines on every order, when export, need test if there info in part field (partxx<>'') generate rows lines have data. there fields in table need populated in each output row, though doesn't change. query first "row" straight-forward, obstacle "if/then" part subsequent rows , generating "line"

the output looking this:

ticket# custid business relationship line shipattn shipadd shipcity shipstate shipzip part qty 123456 cust01 987465 1 joe smith address anytown il 01234 key 2 123456 cust01 987456 2 joe smith address anytown il 01234 lock 2

any direction appreciated.

what looking pivot table (or "crosstab"). every rdbms has own solution that, failed provide yours. exact syntax depends on rdbms.

a standard-sql query this:

select ticket#, custid, account, 1 line, shipattn, shipadd, shipcity, shipstate, shipzip, part01 part, qty01 qty tbl part01 not null union select ticket#, custid, account, 2 line, shipattn, shipadd, shipcity, shipstate, shipzip, part02 part, qty02 qty tbl part02 not null union ... order ticket#, custid, account, line

order by applied finish result, not lastly leg of query. hence don't need subselect or cte.

sql sql-server-2008-r2 export-to-csv