有一张表Test_A

货名    备注
钟表    2011年进货
粮食    2012年进货


还有一张表Test_B

货名    别名
粮食    大米
粮食    米饭


现在希望通过一个视图得到这样一张表，数据如下：


货名    别名一    别名二    备注
钟表                        2011年进货
粮食    大米      米饭      2012年进货





请问该怎么样写这个视图？

--> 测试数据：[tTest_A]
IF OBJECT_ID('[tTest_A]') IS NOT NULL DROP TABLE [tTest_A]
GO 
CREATE TABLE [tTest_A]([货名] VARCHAR(4),[备注] VARCHAR(10))
INSERT [tTest_A]
SELECT '钟表','2011年进货' UNION ALL
SELECT '粮食','2012年进货'


--> 测试数据：[Test_B]
IF OBJECT_ID('[Test_B]') IS NOT NULL DROP TABLE [Test_B]
GO 
CREATE TABLE [Test_B]([货名] VARCHAR(4),[别名] VARCHAR(4))
INSERT [Test_B]
SELECT '粮食','大米' UNION ALL
SELECT '粮食','米饭'
--------------开始查询--------------------------
--SELECT a.[货名],CASE b.别名 WHEN b.[货名]=a.[货名] then FROM [tTest_A]
----------------结果----------------------------
/* 
*/
DECLARE @s VARCHAR(MAX)
SELECT  @s = ISNULL(@s + ',', '') + QUOTENAME(row_id)
FROM    (
          SELECT  row_number() OVER ( PARTITION BY a.货名 ORDER BY b.别名 ) AS row_id
          FROM    [tTest_A] AS a
          INNER JOIN [Test_B] AS b
          ON      a.[货名] = b.[货名]
        ) t

SELECT  @s ='
SELECT  *
FROM    (
          SELECT  a.货名 ,
                  a.备注 ,
                  b.别名 ,
                  row_number() OVER ( PARTITION BY a.货名 ORDER BY b.别名 ) AS row_id
          FROM    [tTest_A] AS a
          LEFT JOIN [Test_B] AS b
          ON      a.[货名] = b.[货名]
        ) a
PIVOT (MAX(别名) FOR row_id IN('+@s+'))b
'
EXEC(@s)
----------------结果----------------------------
/* 
货名   备注         1    2
---- ---------- ---- ----
钟表   2011年进货    NULL NULL
粮食   2012年进货    大米   米饭

(2 行受影响)

*/

------解决方案--------------------

SQL code

use db;
IF OBJECT_ID('[tTest_A]') IS NOT NULL DROP TABLE [tTest_A]
GO 
CREATE TABLE [tTest_A]([货名] VARCHAR(4),[备注] VARCHAR(10))
INSERT [tTest_A]
SELECT '钟表','2011年进货' UNION ALL
SELECT '粮食','2012年进货'


--> 测试数据：[Test_B]
IF OBJECT_ID('[Test_B]') IS NOT NULL DROP TABLE [Test_B]
GO 
CREATE TABLE [Test_B]([货名] VARCHAR(4),[别名] VARCHAR(4))
INSERT [Test_B]
SELECT '粮食','大米' UNION ALL
SELECT '粮食','米饭' union all
select '粮食','小米';


declare @sql nvarchar(max)=''
declare @s1 nvarchar(max)='';
declare @s2 nvarchar(max)='';
;with cte as (
    select a.货名,a.备注,b.别名,rn=ROW_NUMBER() over(partition by a.货名,a.备注 order by getdate()) from tTest_A a left join test_b b on a.货名=b.货名
),c1 as (
    select w.rn from cte w join cte v on w.货名=v.货名 and w.备注=v.备注 
    where v.rn =(select top 1 rn from cte order by rn desc)
)
select @s1='select 货名,备注'+(select ',['+CAST(rn as varchar(10))+'] as [别名'+CAST(rn as varchar(10))+']' 
from c1 for xml path('')), @s2='max(别名) for rn in ('+STUFF((select ',['+CAST(rn as varchar(10))+']' from c1 for xml path('')),1,1,'')+')';

set @sql=@s1+' from  (select a.货名,a.备注,b.别名,rn=ROW_NUMBER() over(partition by a.货名,a.备注 order by getdate()) from tTest_A a left join test_b b on a.货名=b.货名) w pivot ('+@s2+') p'
exec(@sql)

/*
货名   备注         别名1  别名2  别名3
---- ---------- ---- ---- ----
钟表   2011年进货    NULL NULL NULL
粮食   2012年进货    大米   米饭   小米
*/

/*--对应的静态
select 货名,备注,[1] as [别名一],[2] as [别名二] from (
select a.货名,a.备注,b.别名,rn=ROW_NUMBER() over(partition by a.货名,a.备注 order by getdate()) from tTest_A a left join test_b b on a.货名=b.货名
) w pivot (max(别名) for rn in ([1],[2])) p
*/

------解决方案--------------------
SQL code

if object_id('Test_A') is not  null
drop table Test_A
go

create  table  Test_A (货名 varchar(8),备注 varchar(40))
insert Test_A
select '钟表','2011年进货' union all
select