日期:2014-05-18  浏览次数:20540 次

一个数据库问题
现在数据中有一个字段 a,b,c,d 
 我要变成

  列名
  a
  b
  c
  d

------解决方案--------------------
SQL code
DECLARE @s VARCHAR(1000)
SET @s = 'a,b,c,d'
--1.
DECLARE @s1 VARCHAR(1000)
SET @s1 = RIGHT(REPLACE(',' + @s, ',', ''' AS s UNION SELECT '''),
                LEN(REPLACE(',' + @s, ',', ''' AS s UNION SELECT ''')) - 12) + ''''
EXEC(@s1)
--2
SELECT  REPLACE(REVERSE(( LEFT(s, CHARINDEX(',', s)) )), ',', '') AS S
FROM    ( SELECT  r ,
                  REVERSE(LEFT(@s, r)) + ',' AS s
          FROM    ( SELECT  ( SELECT COUNT (*) FROM sysobjects WHERE name<= t.name
                            ) AS r
                    FROM    sysobjects t
                  ) a
          WHERE   r <= LEN(@s)
                  AND LEFT(@s + ',', r + 1) LIKE '%,'
        ) t
ORDER BY r

------解决方案--------------------
我发现嫂子写代码巨快而且巨霸气的
------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    ID INT,
    Memo VARCHAR(100)
)
GO
INSERT INTO tba
SELECT 1,'a,b,c,d' UNION
SELECT 2,'e,f,g,h,k'
GO
DECLARE @sql VARCHAR(1000) = ''
SELECT @sql = @sql + 'SELECT ''' + REPLACE(Memo, ',', ''' AS Col UNION SELECT ''') + ''' UNION '
FROM tba

SET @sql = LEFT(@sql,LEN(@sql) - 5)

EXEC (@sql)

Col
a
b
c
d
e
f
g
h
k

------解决方案--------------------
探讨

SQL code
DECLARE @s VARCHAR(1000)
SET @s = 'a,b,c,d'
--1.
DECLARE @s1 VARCHAR(1000)
SET @s1 = RIGHT(REPLACE(',' + @s, ',', ''' AS s UNION SELECT '''),
LEN(REPLACE(',' + @s, ',', ''' ……