❶ 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 行受影響)