sql 有些难度，有些深度，有些头疼!!!

Question

表A:
META_PK  C_NAME  E_NAME   C_SHIP  E_SHIP  Title_1  Title_2  AUTHOR  PROJECT_PIC
1          sdf    adf        dd   asdfw    dsfw        sdf   sdf       dsfasd条件是:where META_PK=1效果:(查询出所有的列名所对应的值)
colName             valMETA_PK              1
C_NAME              sdf
E_NAME              adf
C_SHIP              dd
....                 ...
(必须是列名)        (列名所对应的列值)因为字段比较多（100个以上，变态人搞的），不想用：
select ‘META_PK’as colName ,val=META_PK from 表名 where 条件
union
select ‘C_NAME’as colName ,val=C_NAME from 表名 where 条件寻求合理的解决方案

Answer 1

表A结构:
META_PK   C_NAME   E_NAME   C_SHIP E_SHIP  Title_1  Title_2  AUTHOR  PROJECT_PIC ....
1          sdf      adf      dd     asdfw   dsfw      sdf      sdf     dsfasd

Answer 2

效果:(查询出所有的列名所对应的值)
colName   valMETA_PK    1
C_NAME    sdf
E_NAME    adf
C_SHIP    dd
.... ...
(必须是列名)   (列名所对应的列值)

Answer 3

效果:(查询出所有的列名所对应的值)
colName                                               valMETA_PK                                                1
C_NAME                                                sdf
E_NAME                                                adf
C_SHIP                                                dd
.... ...
(必须是列名) (列名所对应的列值)

Answer 4

如有一个列名为Name,值为1,查询出要为:
colName      val
Name          1

Answer 5

我说你的表里如果有两条记录，怎么显示
META_PK C_NAME E_NAME C_SHIP E_SHIP Title_1 Title_2 AUTHOR PROJECT_PIC
1 sdf adf dd asdfw dsfw sdf sdf dsfasd
1 2 3 4 asdfw dsfw sdf sdf dsfasd这样的
还是说PK是主键，保证只有一行

Answer 6

利用表 sysobjects syscolumns 做动态SQL语句，执行

Answer 7

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL1 INT
,COL2 INT
,COL3 INT
,COL4 INT
,COL5 INT
)
INSERT INTO TB
SELECT 1 ,2,3,4,5 UNION ALL
SELECT 2,3,4,5,6
DECLARE @STR VARCHAR(MAX),@COL1 INT
SELECT @STR='',@COL1=1
SELECT @STR=@STR+'UNION ALL SELECT '''+NAME +''','+NAME+' FROM TB WHERE COL1='+LTRIM(@COL1)
FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('TB')
SELECT @STR=STUFF(@STR,1,9,'')
EXEC (@STR)
/*
COL1 1
COL2 2
COL3 3
COL4 4
COL5 5
*/

Answer 8

不知是否写成函数，返回一个table,sql 里面的函数不支持，exec 语句

Answer 9

果然变态!
create table 表A(META_PK int,C_NAME nvarchar(10),E_NAME nvarchar(10),C_SHIP nvarchar(10),E_SHIP nvarchar(10),
  Title_1 nvarchar(10),Title_2 nvarchar(10),AUTHOR nvarchar(10),PROJECT_PIC nvarchar(10))
insert into 表A select 1,'sdf','adf','dd','asdfw','dsfw','sdf','sdf','dsfasd'
create table tb(rm int,val nvarchar(20))
go
insert into tb
select row_number() over(order by b.colid)rm,b.name
from sysobjects a inner join syscolumns b on a.id=b.id 
where a.name='表A'
go
declare @sql nvarchar(4000)
set @sql=''
;with cte as(
select rm,convert(nvarchar(4000),'insert into tb select '+convert(varchar,rm+1000)+' rm,convert(varchar(10),'+val+')val from 表A where META_PK=1') as sqlstr from tb where rm=1
union all
select b.rm,convert(nvarchar(4000),a.sqlstr+' union all select '+convert(varchar,b.rm+1000)+','+b.val+' from 表A where META_PK=1') as sqlstr from cte a inner join tb b on b.rm=a.rm+1
)select @sql=sqlstr from cte a where not exists(select 1 from cte where rm>a.rm)
execute(@sql)
select a.rm,a.val as colname,b.val from tb a inner join tb b on a.rm=b.rm-1000
go
drop table 表A,tb
/*
rm          colname              val
----------- -------------------- --------------------
1           META_PK              1
2           C_NAME               sdf
3           E_NAME               adf
4           C_SHIP               dd
5           E_SHIP               asdfw
6           Title_1              dsfw
7           Title_2              sdf
8           AUTHOR               sdf
9           PROJECT_PIC          dsfasd(9 行受影响)*/

Answer 10

因为字段在100个以前，所以，发觉拼接出来的语句，会缺少，他nnd,定义成varchar(4000),不够使

Answer 11

这样可能短一些:
create table 表A(META_PK int,C_NAME nvarchar(10),E_NAME nvarchar(10),C_SHIP nvarchar(10),E_SHIP nvarchar(10),
  Title_1 nvarchar(10),Title_2 nvarchar(10),AUTHOR nvarchar(10),PROJECT_PIC nvarchar(10))
insert into 表A select 1,'sdf','adf','dd','asdfw','dsfw','sdf','sdf','dsfasd'
create table tb(rm int,val nvarchar(20))
go
insert into tb
select row_number() over(order by b.colid)rm,b.name
from sysobjects a inner join syscolumns b on a.id=b.id 
where a.name='表A'
go
declare @sql nvarchar(4000),@sqlagain nvarchar(4000)
set @sql=''
;with cte as(
select rm,convert(nvarchar(4000),'select @sqlagain=''select '+convert(varchar,rm)+',''''''+convert(varchar,'+val+')+'''''' val') as sqlstr from tb where rm=1
union all
select b.rm,convert(nvarchar(4000),a.sqlstr+' union all select '+convert(varchar,b.rm)+',''''''+'+b.val+'+''''''') as sqlstr from cte a inner join tb b on b.rm=a.rm+1
)select @sql=sqlstr+''' from 表A where META_PK=1' from cte a where not exists(select 1 from cte where rm>a.rm)
update tb set rm=rm+1000
exec SP_EXECUTESQL @sql,N'@sqlagain varchar(4000) output ',@sqlagain output
exec('insert into tb '+ @sqlagain)
select b.rm,b.val as colname,a.val from tb a inner join tb b on a.rm=b.rm-1000
go
drop table 表A,tb
/*
rm          colname              val
----------- -------------------- --------------------
1001        META_PK              1
1002        C_NAME               sdf
1003        E_NAME               adf
1004        C_SHIP               dd
1005        E_SHIP               asdfw
1006        Title_1              dsfw
1007        Title_2              sdf
1008        AUTHOR               sdf
1009        PROJECT_PIC          dsfasd(9 行受影响)*/

Answer 12

改一小错:
--为缩短动态查询语句字长以适应多字段,可:
create table 表A(META_PK int,C_NAME nvarchar(10),E_NAME nvarchar(10),C_SHIP nvarchar(10),E_SHIP nvarchar(10),
  Title_1 nvarchar(10),Title_2 nvarchar(10),AUTHOR nvarchar(10),PROJECT_PIC nvarchar(10))
insert into 表A select 1,'sdf','adf','dd','asdfw','dsfw','sdf','sdf','dsfasd'
create table tb(rm int,val nvarchar(20))
go
insert into tb
select row_number() over(order by b.colid)rm,b.name
from sysobjects a inner join syscolumns b on a.id=b.id 
where a.name='表A'
go
declare @sql nvarchar(4000),@sqlagain nvarchar(4000)
set @sql=''
;with cte as(
select rm,convert(nvarchar(4000),'select @sqlagain=''select '+convert(varchar,rm)+',''''''+convert(varchar,'+val+')+'''''' val') as sqlstr from tb where rm=1
union all
select b.rm,convert(nvarchar(4000),a.sqlstr+' union all select '+convert(varchar,b.rm)+',''''''+'+b.val+'+''''''') as sqlstr from cte a inner join tb b on b.rm=a.rm+1
)select @sql=sqlstr+''' from 表A where META_PK=1' from cte a where not exists(select 1 from cte where rm>a.rm)
update tb set rm=rm+1000
exec SP_EXECUTESQL @sql,N'@sqlagain nvarchar(4000) output ',@sqlagain output
exec('insert into tb '+ @sqlagain)
select b.rm,b.val as colname,a.val from tb a inner join tb b on a.rm=b.rm-1000
go
drop table 表A,tb
/*
rm          colname              val
----------- -------------------- --------------------
1001        META_PK              1
1002        C_NAME               sdf
1003        E_NAME               adf
1004        C_SHIP               dd
1005        E_SHIP               asdfw
1006        Title_1              dsfw
1007        Title_2              sdf
1008        AUTHOR               sdf
1009        PROJECT_PIC          dsfasd(9 行受影响)*/

Answer 13

拼接语句经常会有这个问题那就直接用 exec（'xxxxxx'）执行 就不用怕超过限制了

Answer 14

Following is a generic procedure that could be used to unpivot any table.use tempdb
GO
create table A (META_PK int, C_NAME varchar(100), E_NAME varchar(100), C_SHIP varchar(100), E_SHIP varchar(100), Title_1 varchar(100), Title_2 varchar(100), AUTHOR varchar(100), PROJECT_PIC varchar(100))
insert into A select 1, 'sdf','adf','dd','asdfw','dsfw','sdf','sdf','dsfasd'
GO
Alter procedure usp_UnpivotTable ( @TableName varchar(100), @Where varchar(1000))
AS
declare @colList varchar(8000), @SelectList varchar(8000), @SQL varchar(max)
set @colList = stuff((select ',['+column_name+']' from information_schema.columns where table_name = @TableName for xml path ('')),1,1,'')
set @SelectList = stuff((select ',convert(varchar(max), '+column_name+') as '+column_name from information_schema.columns where table_name = @TableName for xml path ('')),1,1,'')
Set @SQL = 'select ColName, Val from (select '+@SelectList+' from '+@TableName+' '+@Where+' ) as x UNPIVOT (val for ColName in ('+@colList+'))  as y'
Exec (@SQL)
GO
Exec usp_UnpivotTable 'A', 'where META_PK=1'

Answer 15

A correction for my above post:use tempdb
GO
create table A (META_PK int, C_NAME varchar(100), E_NAME varchar(100), C_SHIP varchar(100), E_SHIP varchar(100), Title_1 varchar(100), Title_2 varchar(100), AUTHOR varchar(100), PROJECT_PIC varchar(100))
insert into A select 1, 'sdf','adf','dd','asdfw','dsfw','sdf','sdf','dsfasd'
GO
Create procedure usp_UnpivotTable ( @TableName varchar(100), @Where varchar(1000))
AS
declare @colList varchar(8000), @SelectList varchar(8000), @SQL varchar(max)
set @colList = stuff((select ',['+column_name+']' from information_schema.columns where table_name = @TableName for xml path ('')),1,1,'')
set @SelectList = stuff((select ',convert(varchar(max), '+column_name+') as '+column_name from information_schema.columns where table_name = @TableName for xml path ('')),1,1,'')
Set @SQL = 'select ColName, Val from (select '+@SelectList+' from '+@TableName+' '+@Where+' ) as x UNPIVOT (val for ColName in ('+@colList+'))  as y'
Exec (@SQL)
GO
Exec usp_UnpivotTable 'A', 'where META_PK=1'

Answer 16

I have created an even more generic procedure to give users the option to provide columns (@KeyList) that they don't want to unpivot.
use tempdb
GO
create table A (META_PK int, C_NAME varchar(100), E_NAME varchar(100), C_SHIP varchar(100), E_SHIP varchar(100), Title_1 varchar(100), Title_2 varchar(100), AUTHOR varchar(100), PROJECT_PIC varchar(100))
insert into A select 1, 'sdf', 'adf', 'dd', 'asdfw', 'dsfw', 'sdf', 'sdf', 'dsfasd'
union all     select 2, '2sdf', '2adf', '2dd', '2asdfw', '2dsfw', '2sdf', '2sdf', '2dsfasd'GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UnpivotTable]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_UnpivotTable]
GO
Create procedure usp_UnpivotTable ( 
@TableName varchar(100), 
@KeyList varchar(1000), 
@WhereClause varchar(1000)
) ASDeclare @ColList varchar(8000), @SelectList varchar(8000), @SQL varchar(max)Select @ColList = isnull(@colList+',[','[')+column_name+']'
from information_schema.columns 
where table_name = @TableName 
and charindex(','+column_name+',', ','+isnull(@KeyList,'')+',',1) =0Select @SelectList = isnull(@SelectList+',','')+'CONVERT(VARCHAR(MAX),'+column_name+') AS '+column_name
from information_schema.columns 
where table_name = @TableNameSet @SQL = 'SELECT '+isnull(@KeyList+',','')+' ColName, Val 
FROM (SELECT '+@SelectList+' FROM '+@TableName+' '+ISNULL(@WhereClause,'')+') AS x 
UNPIVOT (Val FOR ColName IN ('+@colList+')) AS y'
Print @SQL -- Check the final SQL statement!!
Exec (@SQL)
GO
--Test Scripts to show how to use this procedure--
Exec usp_UnpivotTable 'A', null, 'where meta_pk=1'
Exec usp_UnpivotTable 'A', 'META_PK', 'where meta_pk=1'
Exec usp_UnpivotTable 'A', 'META_PK,E_NAME', null
Exec usp_UnpivotTable 'A', null, NULL
Happy SQLing...

Answer 17

我狂晕，为啥，我每次试都不成功呢，表里面实际上有157(真想揍那人一顿,搞这么多字段)个字段，主键为:guid值

Answer 18

Hi, we have time difference.Have you tried the script in "#24楼"? If it doesn't work in your database, please let me know the error message. BTW, I think that is a very useful procedure if you are going to do some work like this. I am going to add it to my toolbox.

Answer 19

提示字符串或二进制被截取的error