表A
==========================
itemID itemName
--------------------------
1 name
2 age
3 sex
.................
表B
============================
itemID itemValue
1 张三
2 20
3 男
1 李四
2 19
3 女
.....................
我希望得到的视图是:name age sex
==========================
张三 20 男
李四 19 女
....................说明:表A的内容实际是段字,表B的内容实际是字段的值,请问我这个视图应该如何写?
==========================
itemID itemName
--------------------------
1 name
2 age
3 sex
.................
表B
============================
itemID itemValue
1 张三
2 20
3 男
1 李四
2 19
3 女
.....................
我希望得到的视图是:name age sex
==========================
张三 20 男
李四 19 女
....................说明:表A的内容实际是段字,表B的内容实际是字段的值,请问我这个视图应该如何写?
declare @A table([itemID] int,[itemName] varchar(4))
insert @A
select 1,'name' union all
select 2,'age' union all
select 3,'sex'
--> 测试数据:@B
declare @B table([itemID] int,[itemValue] varchar(4))
insert @B
select 1,'张三' union all
select 2,'20' union all
select 3,'男' union all
select 1,'李四' union all
select 2,'19' union all
select 3,'女'
select name, age,sex from
(
select p=row_number() over(partition by [itemName] order by [itemName]),[itemName],[itemValue] from @a a join @b b on a.[itemID]=b.[itemID]
) p
pivot
(
max([itemValue]) for [itemName] in( name , age , sex )
) p/*
name age sex
---- ---- ----
张三 20 男
李四 19 女(2 行受影响)*/
insert into b values(1 , '张三')
insert into b values(2 , '20')
insert into b values(3 , '男')
insert into b values(1 , '李四')
insert into b values(2 , '19')
insert into b values(3 , '女')
goselect * , id = identity(int,1,1) into tmp from bselect
max(case itemid when 1 then itemvalue end) [name],
max(case itemid when 2 then itemvalue end) [age],
max(case itemid when 3 then itemvalue end) [sex ]
from tmp
group by (id - 1)/3drop table b , tmp /*
name age sex
---------- ---------- ----------
张三 20 男
李四 19 女
*/
AS
SELECT
MAX(CASE WHEN itemName='NAME' THEN itemValue END) AS NAME,
MAX(CASE WHEN itemName='AGE' THEN itemValue END )AS AGE
FROM
(SELECT A.itemID,itemName,itemValue FROM A ,B WHERE A.itemID=B.itemID)AS T
GROUP BY itemID???死的
(itemID int
,itemName varchar(10))insert into t1 select 1 ,'name'
insert into t1 select 2 ,'age'
insert into t1 select 3 ,'sex'
create table t2
(itemID int
,itemValue nvarchar(10))insert into t2 select 1 ,N'张三'
insert into t2 select 2 ,N'20'
insert into t2 select 3 ,N'男'
insert into t2 select 1 ,N'李四'
insert into t2 select 2 ,N'19'
insert into t2 select 3 ,N'女'
select * from t1
select * from t2select * , id = identity(int,1,1) into #taoistong from t2
select
max(case itemid when 1 then itemvalue end) [name],
max(case itemid when 2 then itemvalue end) [age],
max(case itemid when 3 then itemvalue end) [sex ]
from #taoistong
group by (id - 1)/3
改成动态的exec去执行,要是视图的话,好像不行
insert into a values(1 , 'name')
insert into a values(2 , 'age')
insert into a values(3 , 'sex')
create table b(itemID int, itemValue varchar(10))
insert into b values(1 , '张三')
insert into b values(2 , '20')
insert into b values(3 , '男')
insert into b values(1 , '李四')
insert into b values(2 , '19')
insert into b values(3 , '女')
goselect * , id = identity(int,1,1) into tmp from bdeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',max(case itemName when ''' + itemName + ''' then t.itemValue end) [' + itemName + ']'
from (select distinct itemName from a) as t
set @sql = 'select ' + substring(@sql,2,len(@sql)) + ' from tmp t , a where t.itemID = a.itemID group by (t.id - 1)/3'
exec(@sql) drop table a,b , tmp /*
age name sex
---------- ---------- ----------
20 张三 男
19 李四 女警告: 聚合或其它 SET 操作消除了空值。
*/
EXEC('
CREATE VIEW WWW
AS
SELECT * FROM TB')
insert A select
1 ,'name' union select
2 ,'age' union select
3 ,'sex'
create table b (itemID int, itemValue varchar(10))
insert b select
1, '张三' union select
2, '20' union select
3, '男' union select
1, '李四' union select
2, '19' union select
3, '女'
goselect id ,itemname,itemvalue
INTO #P5
from A join (select ROW_NUMBER() over(order by getdate()) as id,* from b ) k on A.itemID=k.itemID declare @s varchar(8000)
set @s=''
select @s=stuff((@s+', max(CASE WHEN ITEMNAME = '''+ITEMNAME+''' THEN itemValue ELSE '''' END )AS ['+ITEMNAME+']'),1,1,'')
FROM (select distinct itemname from #p5 ) k
exec ('select '+@s+' from #p5 group by (id-1)/3 ')/*
age name sex
---------- ---------- ----------
20 张三 男
19 李四 女*/
insert A select
1 ,'name' union select
2 ,'age' union select
3 ,'sex'
create table b (itemID int, itemValue varchar(10))
insert b select
1, '张三' union select
2, '20' union select
3, '男' union select
1, '李四' union select
2, '19' union select
3, '女'
go
--创建空表
declare @s varchar(8000)
set @s=''
SELECT @s=STUFF((@s+','+itemname+' VARCHAR(30)'),1,1,'(')
FROM (SELECT DISTINCT itemname FROM A ) L
SELECT @S
EXEC ('CREATE TABLE OPO '+@S+')')select id ,itemname,itemvalue
INTO #P5
from A join (select ROW_NUMBER() over(order by getdate()) as id,* from b ) k on A.itemID=k.itemID
declare @ss varchar(8000)
set @ss=''
select @ss=stuff((@ss+', max(CASE WHEN ITEMNAME = '''+ITEMNAME+''' THEN itemValue ELSE '''' END )AS ['+ITEMNAME+']'),1,1,'')
FROM (select distinct itemname from #p5 ) k
INSERT opo
exec ('select '+@ss+' from #p5 group by (id-1)/3 ')
--创建视图
create view v_so
as
select * from opo
--检测
select * from v_so/*
age name sex
---------- ---------- ----------
20 张三 男
19 李四 女*/