表1
字段名:F
值:(行不固定)
-----------------
FIELD1
FIELD2
FIELD3
....表2
字段名:V
值:(行不固定)
-----------------
VALUE1
VALUE2
VALUE3
....如何查询生成表
表3
字段名:
FIELD1,FIELD2,FIELD3,...值:
-----------------
VALUE1,VALUE2,VALUE3,...
字段名:F
值:(行不固定)
-----------------
FIELD1
FIELD2
FIELD3
....表2
字段名:V
值:(行不固定)
-----------------
VALUE1
VALUE2
VALUE3
....如何查询生成表
表3
字段名:
FIELD1,FIELD2,FIELD3,...值:
-----------------
VALUE1,VALUE2,VALUE3,...
例子
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
godeclare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) drop table tb/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74(2 row(s) affected)
(
nname varchar(2),
abc varchar(1),
num int
)insert into #t_1select '甲','B','1'
union
select '乙','C','2'
union
select '丙','A','1'
union
select '甲','C','1'
union
select '乙','A','2'
union
select '丙','B','2'
union
select '甲','A','3'
union
select '乙','B','1'
union
select '丙','C','3'create table #t_2
(
nname varchar(2),
a int,
b int,
c int,
num int
)
insert into #t_2
select nname,a=max(case abc when 'a' then num else 0 end),b=max(case abc when 'b' then num else 0 end),
c=max(case abc when 'c' then num else 0 end)
from #t_1
group by nnamedrop table #t_1
而且表1和表2都是存储过程中生成的临时表,如何增加自增列,我是SQL新手,还请详细说明,先摆谢了
@TableName sysname,
@Fields nvarchar(max),
@Values nvarchar(max)--,
--@Created datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cmd nvarchar(max);
-- 旋转字段名
CREATE TABLE #FieldName (Fieldname sysname);
INSERT INTO #FieldName
SELECT Fieldname FROM (SELECT
CONVERT(xml,'<v><![CDATA[' +
REPLACE(@Fields,';',']]></v><v><![CDATA[')+']]></v>') AS Fieldnames
) AS A
CROSS APPLY (
SELECT T.x.value('.','varchar(100)') AS Fieldname
FROM A.Fieldnames.nodes('//v') AS T(x)
) AS B
-- 旋转值
CREATE TABLE #FieldValue (FieldValue sysname);
INSERT INTO #FieldValue
SELECT FieldValue FROM (SELECT
CONVERT(xml,'<v><![CDATA[' +
REPLACE(@Values,';',']]></v><v><![CDATA[')+']]></v>') AS FieldValues
) AS A
CROSS APPLY (
SELECT T.x.value('.','varchar(100)') AS FieldValue
FROM A.FieldValues.nodes('//v') AS T(x)
) AS B
-- 判断是存在表单 @Table ,不存在则自动创建表单
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = @TableName)
BEGIN
SET @cmd = 'CREATE TABLE [dbo].[' + @TableName + ']([id] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY CLUSTERED ([id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]';
EXEC(@cmd);
END -- 判断表单@Table是否存在@Fields列出的所有字段,不存在则自动创建
SET @cmd = '';
SELECT
@cmd = @cmd + 'ALTER TABLE [' + @TableName + '] ADD '
+ FieldName + ' nvarchar(max);' + CHAR(13) + CHAR(10)
FROM #FieldName AS A
WHERE NOT EXISTS(
SELECT * FROM sys.columns
WHERE [name] = A.FieldName AND object_id = OBJECT_ID(@TableName)
);
EXEC(@cmd);
-- 插入数据@Values数据
SET NOCOUNT OFF;
END
GO