时间戳

首页 > 解决方案 > 为可变数量的列连接动态旋转的列

sql-server - 为可变数量的列连接动态旋转的列

问题描述

我已经能够成功地将不同的字段值旋转到单独的列中，但我想要将所有这些单独的（旋转的）列连接到一列中。我不知道在运行时最终有多少旋转列，所以我不能只使用 Column1 + Column2 + Column3 等。

这是我的sql

--Create a table variable to hold my source data

declare @datatable  table
(
    OrderId             int,
    ProductId           int,
    ClientName          varchar(50)
)

--insert some data
insert into         @datatable values (1, 2, 'Joe Bloggs')
insert into         @datatable values (1, 2, 'Frank Bloggs')

--Create a temp table

--that introduces a new field (called Header)
--to give me column names for my pivoted data

IF OBJECT_ID('tempdb..#PivotedClients') IS NOT NULL DROP TABLE #PivotedClients

create table        #PivotedClients
            (
               OrderId  int,
               ProductId    int,
               ClientName   varchar(50),
               Header   varchar(100)
            )

insert into     #PivotedClients
  select            OrderId,
            ProductId,
            ClientName,
            'Client ' + Cast(Rank() Over (Partition by OrderId                   
  order by ClientName) as varchar(3)) 
  from          @datatable

--Create variables to hold my column names
--and my (dynamic) sql

declare @pivotcolumns       nvarchar(max),
    @colsWithNoNulls    nvarchar(max),
    @sqlquery       nvarchar(max)

set @pivotcolumns = STUFF(
                (
                  select distinct ',' + QUOTENAME(Header)
                  from #PivotedClients
                  for XML PATH (''), TYPE
                        ).value('.', 'nvarchar(max)')
                ,1,1,'')

set @colsWithNoNulls = STUFF(
                  (
                               SELECT DISTINCT ',ISNULL(' + QUOTENAME(Header) + ', '''') ' + QUOTENAME(Header)
                                        FROM #PivotedClients
         for XML PATH (''), TYPE
    ).value('.', 'NVARCHAR(max)')
    ,1,1,''
        )

if OBJECT_ID('tempdb..##Clients ') is not null drop TABLE ##Clients

set @sqlquery = 'select distinct OrderId,
                         ProductID,
                    ' + @colsWithNoNulls + ' 
            into        ##Clients
            from
                (
                   select   OrderId,
                                        ClientName,
                            ProductID,
                        Header
                       from #PivotedClients) x
                pivot 
                (
                   Max(ClientName)
                   for Header in (' + @pivotcolumns + ')
                )   p' 

exec sp_executesql @sqlquery

----
select *
from ##Clients
----

我目前最终获得的记录集是：

OrderId ProductId  Client1       Client 2
1       2          Frank Bloggs  Joe Blogs

我想要的是：

OrderID ProductId Clients
1       2         Frank Bloggs Joe Bloggs

我不知道在运行时最终会得到多少“透视”列，所以我不能只使用 Client1 + Client2 等

标签： sql-serverpivotstring-concatenation

解决方案

只需要更改您@colsWithNoNulls的执行字符串连接

set @colsWithNoNulls = STUFF(
                (
                    SELECT  DISTINCT '+ '' '' +  ISNULL(' + QUOTENAME(Header) + ', '''')' 
                                        FROM    #PivotedClients
                    for XML PATH (''), TYPE
                ).value('.', 'NVARCHAR(max)')
                , 1, 1, '') + 'AS Clients'

你也可以做一个print @sqlquerybefore sp_executesql。这将帮助您调试动态查询

推荐阅读