❶ sql 将多列数据转换成多行显示
select a as d from t1
union all
select b from t1
union all
select c from t1
通用只可用动态
declare @s nvarchar(4000)
select @s=isnull(@s+'union all select ','select ')+Name+' as D from 表名' from syscolumns where ID=object_id('表名')
exec(@s)
❷ sql语句实现多列转多行
其实本质就是行转列 推荐一篇文章希望对你有帮助，2楼的也是一种办法。
http://blog.csdn.net/you_tube/archive/2009/04/10/4059251.aspx
❸ ms sql怎么把多行变为多列
这个网上有代码的，sql行转列，不行就把你的字段贴出来
❹ MS SQL SERVER 如何把多列的值 , 变成一列多行 .
行转列，列转行是我们在开发过程中经常碰到的问题
行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。
但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT...CASE 语句中所指定的语法更简单、更具可读性.
--行转列的静态方案一：CASE WHEN,兼容sql2000
select custid,
3 sum(case when YEAR(orderdate)=2002 then qty end) as [2002],
sum(case when YEAR(orderdate)=2003 then qty end) as [2003],
sum(case when YEAR(orderdate)=2004 then qty end) as [2004]
from orders
group by custid;--行转列的静态方案二：PIVOT,sql2005及以后版本
select *
from (select custid,YEAR(orderdate) as years,qty from orders) as ord
pivot(sum(qty) for years in([2002],[2003],[2004]))as p
❺ SQL server把少列多行拼接成多列多行
SELECT[date],
MAX(CASEWHEN[id]='a'THEN[金额]ELSENULLEND)AS'a.金额',
MAX(CASEWHEN[id]='a'THEN[笔数]ELSENULLEND)AS'a.笔数',
MAX(CASEWHEN[id]='b'THEN[金额]ELSENULLEND)AS'b.金额',
MAX(CASEWHEN[id]='b'THEN[笔数]ELSENULLEND)AS'b.笔数',
MAX(CASEWHEN[id]='c'THEN[金额]ELSENULLEND)AS'c.金额',
MAX(CASEWHEN[id]='c'THEN[笔数]ELSENULLEND)AS'c.笔数',
MAX(CASEWHEN[id]='d'THEN[金额]ELSENULLEND)AS'd.金额',
MAX(CASEWHEN[id]='d'THEN[笔数]ELSENULLEND)AS'd.笔数',
MAX(CASEWHEN[id]='e'THEN[金额]ELSENULLEND)AS'e.金额',
MAX(CASEWHEN[id]='e'THEN[笔数]ELSENULLEND)AS'e.笔数'
FROM表XXXGROUPBY[date];
❻ sql数据库按符号拆分成多列和多行
你这样是把列2看成是一个多值数据了，用普通的sql是处理不了的。用plsql写个小过程来做很容易啊，把一行数据读出来，然后对列2进行逐个字符读取放到临时字符，遇到换行的就把列1和临时字符写到数据库，然后继续读
❼ sql多行多列数据怎么弄成一行多列
做个带参数的存储过程就行
❽ sql 一行数据改成多行
您的方法（不建视图）：
SELECT TOP 100 PERCENT *
FROM (SELECT *, SUBSTRING(Picture, 32, 30) AS PICTUREV FROM dbo.procts
WHERE (SUBSTRING(Picture, 32, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 63, 30) AS PICTUREV FROM dbo.procts
WHERE (SUBSTRING(PictureB, 63, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 94, 30) AS PICTUREV FROM dbo.procts
WHERE (SUBSTRING(PictureB, 94, 30) <> '')
) DERIVEDTBL
ORDER BY ...
我觉得您的子串应该是这样计算的,：
SUBSTRING(Picture, 0*31+1,30)
SUBSTRING(Picture, 1*31+1,30)
SUBSTRING(Picture, 2*31+1,30)
SUBSTRING(Picture, 3*31+1,30)
所以，在语句中这样写也会更好写，更容易理解，和更不容易出错。也不影响效率。
你的方法除了多余建了那么多视图，应该说是不错的方法了，但是实现不了不等长文件名按分隔符分离字串。
如果是分隔符为字串分段，您就必须要用我的自定义函数加我的语句。
如果是您写的那样，是固定长度的字串分段，那么就完全用你的方法，便是稍作改动，不用建中间视图
另外，您喜欢视图的话，可以建一个视图：
create view DERIVEDTBL
as
create view
SELECT *, SUBSTRING(Picture, 0*31+1,30) AS PICTUREV FROM dbo.procts WHERE (SUBSTRING(Picture, 32, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 1*31+1,30, 30) AS PICTUREV FROM dbo.procts WHERE (SUBSTRING(PictureB, 63, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 2*31+1,30, 30) AS PICTUREV FROM dbo.procts WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 3*31+1,30, 30) AS PICTUREV FROM dbo.procts WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 4*31+1,30, 30) AS PICTUREV FROM dbo.procts WHERE (SUBSTRING(PictureB, 94, 30) <> '')
union
SELECT *, SUBSTRING(Picture, 5*31+1,30, 30) AS PICTUREV FROM dbo.procts WHERE (SUBSTRING(PictureB, 94, 30) <> '')
--....向下任意延伸，只要改SUBSTRING中的序号就行了。
go
调用时这样就行了：
SELECT TOP N *
FROM DERIVEDTBL
ORDER BY ...
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
一句解决问题，这样：
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,1,'|') AS PICTURE,1 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,2,'|') AS PICTURE,2 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,3,'|') AS PICTURE,3 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,4,'|') AS PICTURE,4 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,5,'|') AS PICTURE,5 AS SN from TABLENAME
UNION
SELECT ID,TITLE,DBO.MYSPLIT(PICTURES,6,'|') AS PICTURE,6 AS SN from TABLENAME
-- . . . . . .
ORDER BY ID,TITLE,SN
还有，您的这个函数求等长字串，不知道为什么没有首字串？
SUBSTRING(Picture, 32, 30)
就是这个个？
SUBSTRING(Picture, 1, 30)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
这里只给您写了6行,来满足您每ID最多6张图片的要求。写多少行都可以，即每ID最大图片数为6张。您可以写100行，1000行都行！
这里还给您多加了以个序号字段（SN），以保证图片顺序与原排列相同。如果不需要您可以去掉他。
下面的函数是一个非常有用的自定义数，是用来提取字串中某一部份的，请您执行一下，把他建立到您的数据库中，今后对您的编程会提供很大的方便。当然，本例所用的函数就是这个函数。
create FUNCTION mysplit--将以某分隔符分段的字串,按指定的顺序号提取子串
(@str nvarchar(2000),--源字串
@sn int, --提取序号
@Deli varchar(1) --分隔符
)
RETURNS varchar(100)
AS
BEGIN
declare @first int,@last int,@result varchar(1000),@sn0 int
select @sn0=0,@first=0,@LAST=1,@str=@str+REPLICATE(@DELI,1)
while @sn0!=@sn
begin
select @sn0=@sn0+1,@first=@LAST,@last=charindex(@DELI,@str,@LAST)+1
end
if @last-@first-1<0
set @result=''
else
SET @RESULT=SUBSTRING(@str,@FIRST,@LAST-@FIRST-1)
RETURN ( @RESULT )
END
GO
❾ sql多列合并为一列多行
CREATE
TABLE
#t
(
id
int,
a
char(2),
b
char(2),
c
char(2),
d
char(2)
)
go
INSERT
INTO
#t
SELECT
1,
'a1',
'b1',
'c1',
'd1'
UNION
ALL
SELECT
2,
'a2',
'b2',
'c2',
'd2'
UNION
ALL
SELECT
3,
'a3',
'b3',
'c3',
'd3'
GO
1>
SELECT
2>
CASE
3>
WHEN
ROW_NUMBER()
OVER(partition
by
id
order
by
[all])
=
1
THEN
cast(id
as
varchar(2))
4>
ELSE
''
END
AS
id,
5>
[all]
6>
FROM
7>
(
8>
SELECT
id,
a
AS
[all]
FROM
#t
9>
UNION
ALL
10>
SELECT
id,
b
AS
[all]
FROM
#t
11>
UNION
ALL
12>
SELECT
id,
c
AS
[all]
FROM
#t
13>
UNION
ALL
14>
SELECT
id,
d
AS
[all]
FROM
#t
15>
)
t
16>
GO
id
all
--
---
1
a1
b1
c1
d1
2
a2
b2
c2
d2
3
a3
b3
c3
d3
(12
行受影响)
❿ SQL 单列转多列多行!
以下 SQL 在 SQL Server 2008 Express 版本下，测试通过。
CREATE TABLE #temp (
name VARCHAR(10)
);
INSERT INTO #temp
SELECT '中国' UNION ALL
SELECT '美国' UNION ALL
SELECT '英国' UNION ALL
SELECT '德国' UNION ALL
SELECT '马来西亚' UNION ALL
SELECT '泰国' UNION ALL
SELECT '西班牙' UNION ALL
SELECT '越南' UNION ALL
SELECT '韩国' UNION ALL
SELECT '日本'
GO
SELECT
MAX( CASE WHEN Name_ID = 1 THEN name ELSE '' END ) AS 字段名称1,
MAX( CASE WHEN Name_ID = 2 THEN name ELSE '' END ) AS 字段名称2,
MAX( CASE WHEN Name_ID = 3 THEN name ELSE '' END ) AS 字段名称3,
MAX( CASE WHEN Name_ID = 0 THEN name ELSE '' END ) AS 字段名称4
FROM
(
SELECT
(ROW_NUMBER() OVER ( ORDER BY (SELECT 1) ) - 1) / 4 AS Line,
ROW_NUMBER() OVER ( ORDER BY (SELECT 1) ) % 4 AS Name_ID,
name
FROM
#temp
) SubQuery
GROUP BY
Line
GO
执行结果:
字段名称1 字段名称2 字段名称3 字段名称4
---------- ---------- ---------- ----------
中国 美国 英国 德国
马来西亚 泰国 西班牙 越南
韩国 日本
(3 行受影响)