带透视子句的动态SQL Server查询中的语法错误 [英] Syntax error in dynamic sql server query with pivot clause

问题描述

 Chromosome Locus       Variant_A   Variant_B   Variant Strain_ID   Family  Parent1_Name    Parent1_Marker  Parent2_Name    Parent2_Marker  Line    Marker  Gid
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Gm09        40907915    G           A           GA      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      BB      2
Gm09        422384      G           A           GA      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      AA      4
Gm09        422720      A           G           AG      DS11.46096  46      IA3023          BB              PI507.681B*     AA              96      BB      5
Gm09        424439      C           A           CA      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      AA      7
Gm09        425375      G           T           GT      DS11.46096  46      IA3023          AA              PI507.681B*     BB              96      AA      9
Gm09        425581      T           C           TC      DS11.46096  46      IA3023          BB              PI507.681B*     AA              96      BB      10
Gm09        43921862    C           A           CA      DS11.46096  46      IA3023          BB              PI507.681B*     AA              96      AA      12

我已经附上了桌子的图像.该表在Strain_ID中的各个ID，每个ID在不同的基因座中都有多个标记.我想在标记上聚集的基因座上使用pivot，这样我就可以在行中使用单独的strain_Ids，而在列中使用所有基因座.

I've attached the image of my table. This table individual ids in Strain_ID and each Id has several markers in different loci. I want to pivot on Loci aggregating on Marker, so that I can have individual strain_Ids in rows and all the loci as columns.

这是我在Sql Server 2012 Management Studio上使用的脚本:

This is the script I use on Sql server 2012 Management studio:

declare @cols varchar(Max) 
declare @cols1 varchar(Max)     

set @cols ='[' ;

select @cols += 'D.'+QUOTENAME (Locus) + ','
from(
    select distinct Locus from genotypeQA where Chromosome IN ('Gm01')
) as X 

set @cols= stuff(replace(@cols,'D.[','['),1,1,'')
print @cols

set @cols1 = SUBSTRING(@cols,1,LEN(@cols)-1)
print @cols1

select *  
from (
    select 
    genotypeQA.Strain_ID, 
    genotypeQA.Family,
    '+ @cols +',
    genotypeQA.Marker
from genotypeQA
where 
    genotypeQA.Family IN ('10') 
    AND genotypeQA.Chromosome IN ('Gm01')  
) as D

Pivot( 
MAX(Marker) 
For Locus IN ('+ @cols +')) as p 

我收到以下错误:

Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '+ @cols +'.



I expect the following format of output shown here with part of the table: 


| Strain     |           | Gm09_40907915 | Gm09_422384 | Gm09_422720 | Gm09_424439 |    |
| DS11.46096 | Variant_A | G             | G           | A           | C           |    |
| DS11.46096 | Variant_B | A             | A           | G           | A           |    |
| DS11.46096 | Variant   | GA            | GA          | AG          |        CA    |  |

+ ------------ + ----------- + --------------- + ----- -------- + ------------- + ----------

+------------+-----------+---------------+-------------+-------------+----------

推荐答案

首先，您需要将Variant，Variant_A和Variant_B值带到单列，并将相应值的列名带到另一列，即，必须带UNPIVOT.我在这里使用的是CROSS APPLY而不是UNPIVOT.

First of all you need to bring Variant, Variant_A and Variant_B values to single column and corresponding values column name in another column, ie, you have to UNPIVOT. I have used CROSS APPLY instead of UNPIVOT here.

声明一个变量以获取数据透视表的列名

Declare a variable to get column names for pivoted table

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + ChrLocus + ']', '[' + ChrLocus + ']')
               FROM    
               (
                     SELECT DISTINCT Chromosome+'_'+ CAST(Locus AS VARCHAR(10))ChrLocus 
                     FROM #TEMP
               ) PV 
               ORDER BY ChrLocus

现在旋转结果.我已经在查询中写了逻辑

Now pivot the result. I have written the logic inside the query

DECLARE @query NVARCHAR(MAX)
SET @query = '-- This outer query forms your pivoted result
             SELECT * FROM 
             (
                -- Source data for pivoting
                SELECT DISTINCT Chromosome+''_''+ CAST(Locus AS VARCHAR(10))ChrLocus,Strain_ID,
                Variants,COLNAMES 
                FROM #TEMP 
                CROSS APPLY(VALUES (Variant_A,''Variant_A''),(Variant_B,''Variant_B''),(Variant,''Variant''))
                AS COLUMNNAMES(Variants,COLNAMES)
             ) x
             PIVOT 
             (
                 --Defines the values in each dynamic columns
                 MIN(Variants)
                 -- Get the names from the @cols variable to show as column
                 FOR ChrLocus IN (' + @cols + ')
            ) p            
            ORDER BY Strain_ID;' 

EXEC SP_EXECUTESQL @query

• 单击此处以查看结果
• Click here to view result

这篇关于带透视子句的动态SQL Server查询中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！