Server 2008:需要“transposed”结果集

Question

我在创建查询时遇到了困难。我有以下表格(简化)：

table1

doc_no         line         country
12             1            NL
12             2            US
12             ...          ...
12             m            FR
13             1            NL
13             ...          ...
13             n            GR 
...            ...          ...

table2

doc_no         user_doc
12             123456
13             654321

我需要创建一个查询，将doc_no上的两个表连接起来，并返回以下结果集：

user_doc       country-1     country-2    country-...    country-m
123456         NL            US           ...            FR

user_doc       country-1     country-...  country-n
654321         NL            ...          GR

user_doc       country-...
...            ...

两个表中都没有m，n或doc_ no的设置值。最后，结果集将被限制为查询运行的间隔(可能是30秒或60秒)，因此结果集可以是doc_no的10's。最有可能的是结果集将从1到5 user_doc's，但更多是可能的。

数据库只允许数据提取，因此我不能创建临时表、存储过程、视图等，只能使用本地变量等使用T创建选择类型的查询。

我完全找不到解决办法，所以任何建议都会很好。

Thx，

马丁

Answer 1

这可以通过修改@Bluefeet的动态枢轴策略并使用动态Sql来完成。我假设'Country-X‘标题使用列line的值作为x

DECLARE 
  @pivotCols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX);

-- Grab the distinct Lines to use as column headings 
-- (This is a `GROUP_CONCAT` workaround)
SET @pivotCols = STUFF((SELECT distinct ',' + 
       QUOTENAME('Country-' + CAST(Line AS NVARCHAR(20))) 
            FROM table1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = '
    SELECT user_doc, ' + @pivotCols + '
    FROM
    (
        select ''Country-''+CAST(t1.Line AS NVARCHAR(20)) AS Line,  
                    t1.Country, t2.user_doc
        from table1 t1
            inner join table2 t2 on t1.doc_no = t2.doc_no
     ) x
     pivot 
    (
       MIN(Country)
       for Line in (' + @pivotCols + ')
    ) p';

execute(@query);

如果每个doc_no + line组合有多个国家，则选择最低的国家。如果没有这样的行，则显示Nulls。

SqlFiddle在这里

编辑

下面使用ROW_NUMBER()来添加一个任意计数器，而不是依赖于Line (根据需要更改ORDER BY )。我使用了一个讨厌的#temp表黑客(因为#Temp表也可以被EXEC'ed查询访问)来干涸列的命名。

DECLARE 
  @pivotCols AS NVARCHAR(MAX),
  @aliasCols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX);

select 'Country-'+CAST(y.TheNumber AS NVARCHAR(20)) AS ColName, 
           y.Country,
           y.user_doc
    into #temp
    from 
    ( 
        SELECT 
           t1.Country, t2.user_doc, 
           ROW_NUMBER() OVER (PARTITION BY t1.doc_no ORDER BY LINE ASC) AS TheNumber
        FROM 
           table1 t1
           inner join table2 t2 on t1.doc_no = t2.doc_no
     ) y;

SET @pivotCols = STUFF((SELECT distinct ',' + QUOTENAME(ColName)
            FROM #temp
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = '
    SELECT user_doc, ' + @pivotCols + '
    FROM
    #temp x
    pivot 
    (
       MIN(Country)
       for ColName in (' + @pivotCols + ')
    ) p';

execute(@query);

这里更新了SqlFiddle