22,207
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE A(ID VARCHAR(5), NAME NVARCHAR(4), AGE INT, SEX NVARCHAR(2))
CREATE TABLE B(表名 VARCHAR(5), 列名 VARCHAR(5), 字段长度 INT, 字段组合 NVARCHAR(10))
INSERT INTO A
SELECT 'a01', N'张三', 19, N'男' UNION ALL
SELECT 'a02', N'李四', 20, N'女' UNION ALL
SELECT 'a03', N'王五', 21, N'男'
INSERT INTO B
SELECT 'A', 'ID', 5, N'组合' UNION ALL
SELECT 'A', 'Name',4, N'中文' UNION ALL
SELECT 'A', 'Age', 3, N'数字' UNION ALL
SELECT 'A', 'Sex', 2, N'选择'
SELECT 列名, 列字段 = CASE WHEN 列名 = 'ID' THEN ID
WHEN 列名 = 'NAME' THEN NAME
WHEN 列名 = 'AGE' THEN CONVERT(VARCHAR, AGE)
WHEN 列名 = 'SEX' THEN SEX
END, 字段长度, 字段组合
FROM A
CROSS JOIN B
WHERE A.ID ='a01' AND B.表名 ='A'
IF OBJECT_ID('GETTABLE') IS NOT NULL
DROP PROCEDURE GETTABLE
GO
CREATE PROCEDURE GETTABLE(@TNAME VARCHAR(20), @ID VARCHAR(20))
AS
BEGIN
DECLARE @STR NVARCHAR(2000)
SET @STR = N'SELECT 列名, 列字段 = CASE '
SELECT @STR = @STR + N' WHEN 列名 = ''' + 列名 + ''' THEN ' + CASE WHEN 字段组合 = N'数字' THEN 'CONVERT(VARCHAR, ' + 列名+ ')'
ELSE 列名
END
FROM B
WHERE 表名 = @TNAME
SET @STR = @STR + ' END, 字段长度, 字段组合 FROM A CROSS JOIN B WHERE A.ID =''' + @ID + ''' AND B.表名 = ''' + @TNAME + ''''
--print @str
EXEC(@STR)
END
GO
EXEC GETTABLE @TNAME = 'A', @ID = 'a01'
create proc get_tb (@TABLE VARCHAR(10),@COLUMN_NAME VARCHAR(10))
as
begin
DECLARE @SQL VARCHAR(8000),@SQL1 VARCHAR(8000)
select
@sql=isnull(@sql+',','')+'cast (['+列名+'] as varchar(10)) as'+'['+列名+']',
@sql1=isnull(@sql1+',','')+'['+列名+']'
from B WHERE [表名]=@TABLE
--select @SQL,@SQL1
select @SQL='
SELECT a.列名,a.列字段,b.[字段长度],b.[字段组合] '+'
FROM
(SELECT '+@sql+
'FROM '+@TABLE+' where id='''+@COLUMN_NAME+''') AS p
UNPIVOT
(列字段 FOR 列名 IN
('+@SQL1+')
)AS a, b
where a.列名=b.列名'
exec(@SQL)
end
exec get_tb 'A','a01'