22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT A.RES_NO'
SELECT @SQL=@SQL+',A.SJ_NO ['+CONVERT(VARCHAR,SJ_NO)+'],MAX(CASE A.TITLE WHEN '''+TITLE+''' THEN A.ANS1 ELSE '''' END) ['+TITLE+']'
FROM (SELECT A.TITLE,A.SJ_NO FROM RESCH1_SJ A
INNER JOIN RESCH1_ANS C ON C.SJ_NO=A.SJ_NO)
AS A
PRINT(@sql)
SET @SQL=@SQL+' FROM (SELECT A.RES_NO,A.SJ_NO,A.TITLE,C.ANS1,A.PART_ITM,C.ANS2
FROM RESCH1_SJ AS A
INNER JOIN RESCH1_ANS C ON C.SJ_NO=A.SJ_NO
WHERE 1=1) a GROUP BY a.SJ_NO,A.RES_NO,a.part_itm'
PRINT(@SQL)
exec(@sql)
虽然是写出来了,但是查询结果的排序有问题,然后,根据这个思路应该是1行就得出所有数据,但是显示的内容并不正确
USE [DB_12]
GO
/****** Object: Table [dbo].[RESCH1_ANS] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RESCH1_ANS](
[BIL_ID] [varchar](2) NOT NULL,
[BIL_NO] [varchar](20) NOT NULL,
[USR_ANS] [varchar](12) NOT NULL,
[RES_NO] [varchar](20) NOT NULL,
[SJ_NO] [int] NOT NULL,
[ANS1] [varchar](50) NULL,
[ANS2] [ntext] NULL,
CONSTRAINT [PK_RESCH1_ANS] PRIMARY KEY CLUSTERED
(
[BIL_ID] ASC,
[BIL_NO] ASC,
[USR_ANS] ASC,
[RES_NO] ASC,
[SJ_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [DB_12]
GO
/****** Object: Table [dbo].[RESCH1_OPTION] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RESCH1_OPTION](
[RES_NO] [varchar](20) NOT NULL,
[SJ_NO] [int] NOT NULL,
[ITM] [int] NOT NULL,
[NAME] [nvarchar](200) NULL,
[ISREM] [char](1) NULL,
[SCORE] [numeric](22, 8) NULL,
CONSTRAINT [PK_RESCH1_OPTION] PRIMARY KEY CLUSTERED
(
[RES_NO] ASC,
[SJ_NO] ASC,
[ITM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [DB_12]
GO
/****** Object: Table [dbo].[RESCH1_SJ] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RESCH1_SJ](
[RES_NO] [varchar](20) NOT NULL,
[SJ_NO] [int] NOT NULL,
[TITLE] [nvarchar](400) NULL,
[TYPE_NO] [varchar](20) NULL,
[PART_ITM] [int] NULL,
[ITM_PART] [int] NULL,
CONSTRAINT [PK_RESCH1_SJ] PRIMARY KEY CLUSTERED
(
[RES_NO] ASC,
[SJ_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT A.RES_NO'
SELECT @SQL=@SQL+',MAX(CASE A.TITLE WHEN '''+TITLE+''' THEN C.ANS1 ELSE '''' END )['+TITLE+']'
FROM (SELECT A.TITLE FROM RESCH1_SJ AS A
INNER JOIN RESCH1_OPTION AS B ON A.SJ_NO=B.SJ_NO
INNER JOIN RESCH1_ANS AS C ON C.SJ_NO=B.SJ_NO)
AS A
SET @SQL=@SQL+' FROM (SELECT A.TITLE,C.ANS1
FROM RESCH1_SJ AS A
INNER JOIN RESCH1_ANS C ON C.SJ_NO=B.SJ_NO)'
PRINT(@SQL)
exec(@sql)
我修改后还是会报错
print内容:
SELECT A.RES_NO,MAX(CASE A.TITLE WHEN '2问题1' THEN C.ANS1 ELSE '' END )[2问题1],MAX(CASE A.TITLE WHEN '2问题1' THEN C.ANS1 ELSE '' END )[2问题1],MAX(CASE A.TITLE WHEN '3问题2' THEN C.ANS1 ELSE '' END )[3问题2],MAX(CASE A.TITLE WHEN '3问题2' THEN C.ANS1 ELSE '' END )[3问题2],MAX(CASE A.TITLE WHEN '3问题3' THEN C.ANS1 ELSE '' END )[3问题3],MAX(CASE A.TITLE WHEN '3问题3' THEN C.ANS1 ELSE '' END )[3问题3],MAX(CASE A.TITLE WHEN '2问题2' THEN C.ANS1 ELSE '' END )[2问题2],MAX(CASE A.TITLE WHEN '3问题3' THEN C.ANS1 ELSE '' END )[3问题3],MAX(CASE A.TITLE WHEN '1问题1' THEN C.ANS1 ELSE '' END )[1问题1],MAX(CASE A.TITLE WHEN '1问题1' THEN C.ANS1 ELSE '' END )[1问题1],MAX(CASE A.TITLE WHEN '1问题2' THEN C.ANS1 ELSE '' END )[1问题2],MAX(CASE A.TITLE WHEN '1问题2' THEN C.ANS1 ELSE '' END )[1问题2],MAX(CASE A.TITLE WHEN '问题1' THEN C.ANS1 ELSE '' END )[问题1],MAX(CASE A.TITLE WHEN '问题1' THEN C.ANS1 ELSE '' END )[问题1],MAX(CASE A.TITLE WHEN '问题1' THEN C.ANS1 ELSE '' END )[问题1],MAX(CASE A.TITLE WHEN '问题2' THEN C.ANS1 ELSE '' END )[问题2],MAX(CASE A.TITLE WHEN '问题2' THEN C.ANS1 ELSE '' END )[问题2],MAX(CASE A.TITLE WHEN '问题2' THEN C.ANS1 ELSE '' END )[问题2],MAX(CASE A.TITLE WHEN '问题2' THEN C.ANS1 ELSE '' END )[问题2],MAX(CASE A.TITLE WHEN '问题1' THEN C.ANS1 ELSE '' END )[问题1],MAX(CASE A.TITLE WHEN '1问题3' THEN C.ANS1 ELSE '' END )[1问题3],MAX(CASE A.TITLE WHEN '1问题3' THEN C.ANS1 ELSE '' END )[1问题3],MAX(CASE A.TITLE WHEN '1问题3' THEN C.ANS1 ELSE '' END )[1问题3] FROM (SELECT A.TITLE,C.ANS1
FROM RESCH1_SJ AS A
INNER JOIN RESCH1_ANS C ON C.SJ_NO=B.SJ_NO)
报错消息
消息 102,级别 15,状态 1,第 3 行
')' 附近有语法错误。
麻烦帮忙看看。declare @sql varchar(8000)
set @sql = 'select a.SJ_NO '
select @sql = @sql + ' , max(case a.TITLE when ''' + TITLE + ''' then c.OPTION else '''' end) [' + OPTION + ']'
from (select distinct a.TITLE from sj as a inner join opt as b on a.SJ_NO=b.SJ_NO inner join ANS as c on b.SJ_NO=C.SJ_NO ) as a
set @sql = @sql + ' from (select distinct a.TITLE from sj as a inner join opt as b on a.SJ_NO=b.SJ_NO inner join ANS as c on b.SJ_NO=C.SJ_NO ) as t group by a.SJ_NO'
exec(@sql)
这样?declare @sql varchar(8000)
set @sql = 'select b.id '
select @sql = @sql + ' , max(case a.TITLE when ''' + TITLE + ''' then c.OPTION else '''' end) [' + OPTION + ']'
from (select distinct a.TITLE from sj as a inner join opt as b on a.SJ_NO=b.SJ_NO inner join ANS as c on b.SJ_NO=C.SJ_NO ) as a
set @sql = @sql + ' from (select distinct a.TITLE from sj as a inner join opt as b on a.SJ_NO=b.SJ_NO inner join ANS as c on b.SJ_NO=C.SJ_NO ) as t group by b.id'
exec(@sql)
猜一个吧。