横竖转换问题
我要做一个质检分析
质检原因表的字段有reason_id, reason_name
质检单的字段有custom_id, reason_id
我现在要做一个质检分析
格式如下：
custom_id reason_name1,reason_name2, .........
关键问题：
1、原因表的记录条数不确定，我要动态创建列
2、记录原来是竖排的，现在要搞成横排
请问大力等高手，如何实现这样的sql语句呢？多谢！
一定给分！
问题点数：0、回复次数：3Top
1 楼mywhsw（bamboo）回复于 2003-12-03 10:59:57 得分 0
--可以参考一下下面的，
--建立表
DECLARE CUR_A CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' and name not in('dtproperties','B_COL_DISTRIBUTE')
DECLARE @TABLENAME VARCHAR(50)
DECLARE @TMPSTR VARCHAR(8000)
declare @TMPSTR2 Varchar(8000)
open CUR_A
FETCH NEXT FROM CUR_A INTO @TABLENAME
SET @TMPSTR=''
SET @TMPSTR2=''
WHILE @@FETCH_STATUS = 0 BEGIN
SET @TMPSTR=@TMPSTR+@TABLENAME +' VARCHAR(2) NULL,'+CHAR(13)
SET @TMPSTR2=@TMPSTR2+
'CONSTRAINT [DF_B_COL_DISTRIBUTE_'+@TABLENAME+'] DEFAULT ('''') FOR ['+@TABLENAME+'],'
+CHAR(13)
FETCH NEXT FROM CUR_A INTO @TABLENAME
END
SET @TMPSTR=('
create table B_COL_DISTRIBUTE
(
COL_NAME VARCHAR(50) NULL,
COL_MEMO VARCHAR(50) NULL,
'+SUBSTRING(@TMPSTR,1,(LEN(@TMPSTR)-2))+')'+char(13)+
'ALTER TABLE B_COL_DISTRIBUTE WITH NOCHECK ADD
CONSTRAINT [DF_B_COL_DISTRIBUTE_COL_NAME] DEFAULT ('''') FOR [COL_NAME],
CONSTRAINT [DF_B_COL_DISTRIBUTE_COL_MEMO] DEFAULT ('''') FOR [COL_MEMO],'+char(13)+
+SUBSTRING(@TMPSTR2,1,(LEN(@TMPSTR2)-2))+ char(13) )
EXEC ('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[B_COL_DISTRIBUTE]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table [dbo].[B_COL_DISTRIBUTE]
')
EXECUTE ( @TMPSTR)
--PRINT @TMPSTR
CLOSE CUR_A
DEALLOCATE CUR_A
go
---插入记录
DECLARE CUR_B CURSOR FOR
SELECT DISTINCT
t2.name AS COLNAME, CONVERT(varchar, t4.[value]) AS COLMEMO
FROM dbo.sysproperties t4 RIGHT OUTER JOIN
dbo.systypes t3 RIGHT OUTER JOIN
dbo.syscolumns t2 ON t3.xtype = t2.xtype RIGHT OUTER JOIN
dbo.sysobjects t1 ON t2.id = t1.id ON t4.id = + t1.id AND
t4.smallid = + t2.colid
WHERE (t1.xtype = 'U' and t1.name not in('dtproperties','B_COL_DISTRIBUTE'))
DECLARE @COL_NAME VARCHAR(1000)
DECLARE @COL_MEMO VARCHAR(1000)
DECLARE @TBL_NAME VARCHAR(1000)
open CUR_B
FETCH NEXT FROM CUR_B INTO @COL_NAME,@COL_MEMO
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO B_COL_DISTRIBUTE (COL_NAME,COL_MEMO) VALUES (@COL_NAME,isnull(@COL_MEMO,''))
DECLARE CUR_C CURSOR FOR
SELECT t1.name
FROM dbo.sysobjects t1 INNER JOIN
dbo.syscolumns t2 ON t1.id = t2.id INNER JOIN
dbo.systypes t3 ON t2.xtype = t3.xtype LEFT OUTER JOIN
dbo.sysproperties t4 ON t1.id = t4.id AND t2.colid = t4.smallid
WHERE (t1.xtype = 'U') and (t2.name=@COL_NAME ) and t1.name not in('dtproperties','B_COL_DISTRIBUTE')
open CUR_C
FETCH NEXT FROM CUR_C INTO @TBL_NAME
WHILE @@FETCH_STATUS = 0 BEGIN
exec('UPDATE B_COL_DISTRIBUTE SET '+@TBL_NAME+'=''√'' WHERE COL_NAME='''+@COL_NAME+'''')
FETCH NEXT FROM CUR_C INTO @TBL_NAME
END
CLOSE CUR_C
DEALLOCATE CUR_C
FETCH NEXT FROM CUR_B INTO @COL_NAME,@COL_MEMO
END
CLOSE CUR_B
DEALLOCATE CUR_B
Top
2 楼CrazyFor（冬眠的鼹鼠）回复于 2003-12-03 11:02:04 得分 0
能不能把N个reason_name组合成分隔符分隔的一个字符串呢,这样做就比较方便,你那样不太好做.Top
3 楼wy_whb（）回复于 2003-12-03 11:12:05 得分 0
你这样做我感觉太繁了，能否有更加简练的做法呢？Top
