with cte as(
select 'OC' as [state],OC_color as [color] from color union
select 'VC' ,VC_color from color union
select 'VD' ,OC_color from color union
select 'OO' ,OC_color from color
)
select a. *,b.color from room as a, cte as b where a.[state]=b.[state]
select 'OC' as [state],OC_color as [color] from color union
select 'VC' ,VC_color from color union
select 'VD' ,OC_color from color union
select 'OO' ,OC_color from color
)
select a. *,b.color from room as a, cte as b where a.[state]=b.[state]
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'with' 附近有语法错误。请问怎么回事?谢谢
create table #t
(
id int identity(1,1),
[state] varchar(10),
[color] int
)
insert #t
select 'OC' ,OC_color union all
select 'VC' ,VC_color from color union all
select 'VD' ,OC_color from color union all
select 'OO' ,OC_color from color select a. *,b.color from room as a, #t as b where a.[state]=b.[state]
drop table #t
go
create table tb1 (OC_color int, VC_color int, VD_color int, OO_color int )
insert into tb1
select '1','2','3','4'
go
select * from tb1
goselect v,c from tb1
unpivot
(
v for c in(OC_color,VC_color,VD_color,OO_color)
)u
--v c
--1 OC_color
--2 VC_color
--3 VD_color
--4 OO_color
先行列转换,在left join