具有重复元素的动态SQL查询

I am trying to build a query using dynamic sql which looks like following,

我尝试使用动态sql构建一个查询，如下所示，

'select dense_rank () over(partition by column1 order by column1), 
       dense_rank () over(partition by column1,column2 order by column2), 
       dense_rank () over(partition by column1,column2,column3 order column3) from tablename'

I have used STUFF to build the query but able to create query only with one columns and could not repeat columns i.e.'partition by column1, column2'. How to achieve this using STUFF or is there other way to do it ?

我使用了一些东西来构建查询，但是只能创建一个列的查询，不能重复列。的分区column1,column2’。如何使用这些东西来实现这个目标，还是有其他的方法来实现它?

3 个解决方案

#1

You can do it with two queries that concatenates string. One outer that builds the column list and one inner that builds the column list for the partition by clause.

您可以使用两个查询来连接字符串。一个外部构建列列表，另一个内部构建分区by子句的列列表。

Sample table:

示例表:

create table T(C1 int, C2 int, C3 int);

Code:

代码:

declare @SQL nvarchar(max);

with C as
(
  select C.name,
         -- Use order by to control order of columns
         row_number() over(order by C.column_id) as rn
  from sys.columns as C
  where object_id = object_id('T') -- Specify the name of the table
                                   -- Optionally filter out any columns that
                                   -- should not be included
)
select @SQL = 'select '+
              stuff((
                    select ',dense_rank() over(partition by '+
                           stuff((
                                 select ','+c2.name
                                 from C as C2
                                 where C2.rn <= C.rn  
                                 order by C2.rn
                                 for xml path(''), type
                                 ).value('text()[1]', 'nvarchar(max)'), 1, 1, '') +
                           ' order by '+C.name+')'
                    from C
                    for xml path(''), type
                    ).value('text()[1]', 'nvarchar(max)'), 1, 1,'')+
              ' from T';

print @SQL;
--exec (@SQL);

Result:

结果:

select dense_rank() over(partition by C3 order by C3),
       dense_rank() over(partition by C3,C2 order by C2),
       dense_rank() over(partition by C3,C2,C1 order by C1) 
from T

Note: STUFF (Transact-SQL) does not concatenate strings. It is used to insert one string into another string. In the code above, stuff is used to remove the leading comma in the concatenated string by inserting an empty string to the first position and overwriting 1 character. The actual concatenation is done by for xml.

注意:STUFF (Transact-SQL)不会连接字符串。它用于将一个字符串插入另一个字符串。在上面的代码中，通过将空字符串插入到第一个位置并覆盖1个字符来删除连接字符串中的前置逗号。实际的连接是由xml完成的。

#2

Declare @col_name varchar(100)
Declare @colnm varchar(max)
Declare @multicol int
Declare @sqlstr varchar(max)
Declare Cur_1 cursor 
for
Select name 
from sys.columns 
where object_id=object_id('tablename')
order by name

OPEN Cur_1  

FETCH NEXT FROM Cur_1   
INTO  @col_name
set @colnm=''
set @multicol=0
WHILE @@FETCH_STATUS = 0  
BEGIN  
    if @multicol=1 
        begin
            set @colnm=@colnm + ',' + @col_name
        end
    else
        begin
            set @colnm= @colnm+ @col_name
        end
    if @multicol=0
        begin
            set @sqlstr='Select dense_rank () over(partition by ' + @colnm  +  ' order by ' + @colnm + '),'
        end
    else
        begin
            set @sqlstr=@sqlstr + char(13) + char(10) + 'dense_rank () over(partition by ' + @colnm  +  ' order by ' + @colnm + '),' 
        end
    FETCH NEXT FROM Cur_1   
    INTO @col_name 
    set @multicol=1 
END   
CLOSE Cur_1;  
DEALLOCATE Cur_1;  

set @sqlstr=left(@sqlstr,len(@sqlstr)-1) + + char(13) + char(10) + 'from tablename'
print @sqlstr

#3

Declare @QueryString Varchar(Max)
Declare @TblNm Varchar(100)
Set @TblNm='[dbo].[SampleTable]'
Set @QueryString=    
        (
            Select ',dense_rank () over(partition by '+ ColNm + ' Order by [' + name + '])' + char(10)  AS [text()]
            From 
                (
                    SELECT name,ColNm = STUFF(
                                            (
                                                SELECT ',[' + name + ']'
                                                FROM sys.columns
                                                where object_id=object_id(@TblNm) and  column_id<=Tbl1.column_id
                                                FOR XML PATH ('')
                                            ), 1, 1, ''
                                        ) 
                    FROM sys.columns Tbl1
                    where object_id=object_id(@TblNm) 
                ) ColDtl
            For XML PATH ('')
        )
Set @QueryString='Select '
                    + substring(@QueryString,2,len(@QueryString))
                    + ' from ' + @TblNm
Select @QueryString as ExecString
--Exec (@QueryString)

#1