现在有个表如下
No Name Value1 A abc1 A def1 B gfi我想通过编写个查询语句让他变成下面这种格式输出,请问怎么写?No NameA ValueA NameB valueB1 A abc B gfi1 A def NULL NULL
No Name Value1 A abc1 A def1 B gfi我想通过编写个查询语句让他变成下面这种格式输出,请问怎么写?No NameA ValueA NameB valueB1 A abc B gfi1 A def NULL NULL
猜你的逻辑真累,要加在后面竟然还没有关键字,要命了,如果按顺序的话创建2张表加个ID
CREATE TABLE [dbo].[temp001](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[value] [nvarchar](50) NULL
) ON [PRIMARY]CREATE TABLE [dbo].[temp002](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[value] [nvarchar](50) NULL
) ON [PRIMARY]--把你的数据创建为表TBL
insert into temp001
([name],[value])
select * from tbl where name = 'A'insert into temp002
([name],[value])
select * from tbl where name = 'B'select a.NO,a.NAME AS NAMEA ,a.VALUE AS VALUEB ,b.NAME AS NAMEB ,b.VALUE AS VALUEB
from temp001 a left join temp002 b
on a.id = b.id
--创建一张放结果的表 名为TBL1 字段为GUID,[NO],[nameA],[valueA],[nameB],[valueB]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl1](
[GUID] [nvarchar](36) NOT NULL CONSTRAINT [DF_tbl1_GUID] DEFAULT (newid()),
[NO] [nvarchar](50) NULL,
[nameA] [nvarchar](50) NULL,
[valueA] [nvarchar](50) NULL,
[nameB] [nvarchar](50) NULL,
[valueB] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl1] PRIMARY KEY CLUSTERED
(
[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into TBL1
([NO],[nameA],[valueA],[nameB],[valueB])
select [NO],[name],[value],null,null from tbl where [name] = 'A'
declare @no as nvarchar(50),@value nvarchar(50) declare aaa cursor
for
select [no],[value] from tbl
where [name] = 'B'
open aaa
fetch next from aaa into @no,@value
while (@@fetch_status=0)
begin
update TBL1
set nameB = 'B',[valueB] = @value
where [guid] in (select top 1 [guid] from tbl1 where [nameB] is null and [NO] = @NO )
fetch next from aaa into @no,@value
end
close aaa
deallocate aaa