dongtan9465 2013-05-17 13:20
浏览 43

使用php提供动态sql数据透视表时出现语法错误

This dynamic pivot sql runs perfectly in MS Studio, however I can't get it to run with php on the server. I get Incorrect syntax near ','. And Incorrect syntax near 'p'. I've tried every type of "execute" I know, plus all kinds of punctuation. Eight hours is enough. Call in the experts please.

SQL:

declare @col varchar(8000)
declare @sql varchar(8000)

if OBJECT_ID('tempdb..#q1') is not null
drop table #q1

if OBJECT_ID('tempdb..#q2') is not null
drop table #q2

select top 5 row_number() over (
    order by PTC_ASSESSMENT.ASSESSMENT_DATE desc
    ) as row, PTC_ASSESSMENT.ASSESSMENT_ID
into #q1
from PTC_ASSESSMENT
inner join PT_BASIC
on PTC_ASSESSMENT.PATIENT_ID = PT_BASIC.PATIENT_ID
inner join SYS_AS_DDF
on PTC_ASSESSMENT.AS_DDF_ID = SYS_AS_DDF.AS_DDF_ID
    and PTC_ASSESSMENT.DISCIPLINE_CODE = SYS_AS_DDF.DISCIPLINE_CODE
where (PT_BASIC.PATIENT_CODE = '60085')
and (PTC_ASSESSMENT.DISCIPLINE_CODE = 'MD')
group by PTC_ASSESSMENT.ASSESSMENT_DATE, PTC_ASSESSMENT.ASSESSMENT_ID

select convert(varchar, ASSESSMENT_DATE, 101) as dates, QUESTION_TEXT, 
case when ENTRY_TEXT is not null then ENTRY_TEXT else ANSWER_TEXT end as   ANSWER_TEXT
into #q2
from VW_ASSESSMENT_DATA
inner join PT_BASIC
on VW_ASSESSMENT_DATA.PATIENT_ID = PT_BASIC.PATIENT_ID
inner join SYS_AS_DDF
on VW_ASSESSMENT_DATA.AS_DDF_ID = SYS_AS_DDF.AS_DDF_ID
inner join #q1
on VW_ASSESSMENT_DATA.ASSESSMENT_ID = #q1.ASSESSMENT_ID
where (PT_BASIC.PATIENT_CODE = '60085')
and (SYS_AS_DDF.DISCIPLINE_CODE = 'MD')
and VW_ASSESSMENT_DATA.QUESTION_ID in ( 22222, 22223, 22237, 22238, 22239, 22241, 22242 )

select @col = COALESCE(@col + ',', '') + QUOTENAME(dates)
from #q2
group by dates

set @sql = 'select QUESTION_TEXT, ' + @col + '
                from (
                    select distinct *
                    from #q2
                    ) p
                pivot
                (
                max(ANSWER_TEXT) 
                for dates 
                in (' + @col + ' )) as pvt'

execute (@sql)

PHP: Use this to fetch it.

$result = mssql_query($sql) or exit ("select query failed");

Entire PHP script:

$sql = "above sql";

$result = mssql_query($sql) or exit ("select query failed");
$numrows = mssql_num_rows($result);

echo "<table width='60%' cellpadding='5%'>
  <tr>";
if ($numrows > 0) { 
 //loop thru the field names to print the correct headers 
   $i = 0; 
   while ($i < mssql_num_fields($result)) 
 { 
  echo "<th>". mssql_field_name($result, $i) . "</th>"; 
$i++; 
 } 
  echo "</tr>"; 
  //display the data 
  while ($rows = mssql_fetch_array($result)) 
{ 
  echo "<tr>"; 
  foreach ($rows as $data) 
    { 
  echo "<td align='left'>". $data . "</td>"; 
} 
    } 
    }else{ 
echo "<tr><td colspan='" . ($i+1) . "'>No patient medical history found</td></tr>"; 
} 
echo "</table>";
  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 C++使用Gunplot
    • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
    • ¥15 matlab数字图像处理频率域滤波
    • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
    • ¥15 ELGamal和paillier计算效率谁快?
    • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
    • ¥15 Arcgis相交分析无法绘制一个或多个图形
    • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
    • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
    • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)