请问，怎么写这样的视图

请教，如何写这样的视图

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) 
  

 

查看更多 下一篇