请教，如何写这样的视图
- SQL code
有一张表Test_A货名 备注钟表 2011年进货粮食 2012年进货还有一张表Test_B货名 别名粮食 大米粮食 米饭现在希望通过一个视图得到这样一张表，数据如下：货名 别名一 别名二 备注钟表 2011年进货粮食 大米 米饭 2012年进货请问该怎么样写这个视图？
[解决办法]
如果B表数据为动态
参照动态列方法
http://blog.csdn.net/roy_88/article/details/6883078
[解决办法]
- SQL code
--> 测试数据：[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 ALLSELECT '粮食','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 ALLSELECT '粮食','米饭'--------------开始查询----------------------------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.[货名] ) tSELECT @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.[货名] ) aPIVOT (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 ALLSELECT '粮食','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 ALLSELECT '粮食','米饭' union allselect '粮食','小米';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 nulldrop table Test_Agocreate table Test_A (货名 varchar(8),备注 varchar(40))insert Test_Aselect '钟表','2011年进货' union allselect '粮食','2012年进货'if object_id('Test_B') is not nulldrop table Test_Bgocreate table Test_B (货名 varchar(8),别名一 varchar(40),别名二 varchar(40))insert Test_Bselect '粮食','大米','米饭'goDECLARE @s VARCHAR(8000)SELECT @s = ISNULL(@s + ',', '') + QUOTENAME(row_id)FROM ( SELECT row_number() OVER ( PARTITION BY a.货名 ORDER BY b.别名 )as rn FROM [tTest_A] AS a INNER JOIN [Test_B] AS b ON a.[货名] = b.[货名] ) tSELECT @s ='SELECT *FROM ( SELECT a.货名 , a.备注 , b.别名 , row_number() OVER ( PARTITION BY a.货名 ORDER BY b.别名 ) as rn FROM [tTest_A] AS a LEFT JOIN [Test_B] AS b ON a.[货名] = b.[货名] ) aPIVOT (MAX(别名) FOR rn IN('+@s+'))b'EXEC(@s)