原文:
SQL SERVER特殊行轉列案列一則
? 今天有個同事找我，他說他有個需求，需要進行行轉列，但是又跟一般的行轉列有些區別，具體需求如下所說，需要將表1的數據轉換為表2的顯示格式.
我想了一下，給出了一個解決方法，具體如下所示（先給出測試數據）
INSERT
INTO
TEST
SELECT
1,??? 1,???
'定型名稱'
,???
'預定型'
??????????
UNION
?
ALL
SELECT
1,??? 2,???
'進布方式'
,???
'調平'
????????????
UNION
?
ALL
SELECT
1,??? 3,???
'21米長定型機開機速度'
,???
'25'
???
UNION
?
ALL
SELECT
1,??? 4,???
'烘箱溫度'
,???
'195'
????????????
UNION
?
ALL
SELECT
1,??? 5,???
'門幅(CM)'
,???
'200-210-210'
????
UNION
?
ALL
SELECT
2,??? 1,???
'過軟'
,???
'na'
?????????????????
UNION
?
ALL
SELECT
2,??? 2,???
'調平'
,???
'na'
?????????????????
UNION
?
ALL
SELECT
2,??? 3,???
'25'
,???
'+/-0.5'
???????????????
UNION
?
ALL
SELECT
2,??? 4,???
'150'
,???
'+/-5℃頭尾烘箱除外'
??
UNION
?
ALL
SELECT
2,??? 5,???
'188-198-198'
,???
'+/-3'
實現其功能的SQL語句如下所示
WITH
T
AS
(
SELECT
?
NO
, ROW_NUMBER()
OVER
(PARTITION?
BY
?
NO
?
ORDER
?
BY
STEP)
AS
ROWID, NAME,
VAlUE
?
FROM
TEST
)
SELECT
?
NO
,?
MAX
(NAME)
AS
NAME,
MAX
(
VALUE
)
AS
?
VALUE
,
MAX
(NAME2)
AS
NAME2 ,
MAX
(VALUE2)
AS
VALUE2
FROM
(
SELECT
?
NO
, NAME
AS
NAME,
VALUE
?
AS
?
VALUE
,
NULL
?
AS
NAME2,
NULL
?
AS
VALUE2
FROM
T
WHERE
ROWID =1
UNION
?
ALL
SELECT
?
NO
,
NULL
?
AS
NAME ,
NULL
?
AS
?
VALUE
, NAME
AS
NAME2,
VALUE
?
AS
VALUE2
FROM
T
WHERE
ROWID =2
) TT
GROUP
?
BY
NO
![clipboard[4]](http://img.it610.com/image/product/0c69eb4a710b4569b71cf58056cce299.png "clipboard[4]")
但是這樣有一個弊端就是同一NO的記錄不定（不知道有多少條記錄），那么上面SQL語句就不知道怎么寫了，好在這個需求每個NO最多只有四條記錄，所以可以寫成下面. 如果記錄數再多的話，這個SQL語句就寫的很糾結。暫時也沒有想到更好的解決方法。
WITH
T
AS
(
SELECT
?
NO
, ROW_NUMBER()
OVER
(PARTITION?
BY
?
NO
?
ORDER
?
BY
STEP)
AS
ROWID, NAME,
VAlUE
?
FROM
TEST
)
SELECT
?
NO
,
MAX
(NAME)?
AS
NAME? ,
MAX
(
VALUE
)?
AS
?
VALUE
?
?? ,
MAX
(NAME2)
AS
NAME2 ,
MAX
(VALUE2)
AS
VALUE2
?? ,
MAX
(NAME3)
AS
NAME3 ,
MAX
(VALUE3)
AS
VALUE3
?? ,
MAX
(NAME4)
AS
NAME4 ,
MAX
(VALUE4)
AS
VALUE4
FROM
(
SELECT
?
NO
, NAME
AS
NAME ,
VALUE
?
AS
?
VALUE
? ,
????
NULL
?
AS
NAME2,
NULL
?
AS
VALUE2 ,
????
NULL
?
AS
NAME3,
NULL
?
AS
VALUE3 ,
????
NULL
?
AS
NAME4,
NULL
?
AS
VALUE4
FROM
T
WHERE
ROWID =1
UNION
?
ALL
SELECT
?
NO
,
NULL
?
AS
NAME ,?
NULL
?
AS
?
VALUE
? ,
???? NAME
AS
NAME2,
VALUE
?
AS
VALUE2 ,
????
NULL
?
AS
NAME3,
NULL
?
AS
VALUE3 ,
????
NULL
?
AS
NAME3,
NULL
?
AS
VALUE4
FROM
T
WHERE
ROWID =2
UNION
?
ALL
SELECT
?
NO
,
NULL
?
AS
NAME ,
NULL
?
AS
?
VALUE
? ,
????
NULL
?
AS
NAME2,
NULL
?
AS
VALUE2 ,
???? NAME
AS
NAME3,
VALUE
?
AS
VALUE3 ,
????
NULL
?
AS
NAME4,
NULL
?
AS
VALUE4
FROM
T
WHERE
ROWID =3
UNION
?
ALL
SELECT
?
NO
,
NULL
?
AS
NAME ,
NULL
?
AS
?
VALUE
? ,
????
NULL
?
AS
NAME2,
NULL
?
AS
VALUE2 ,
????
NULL
?
AS
NAME3,
NULL
?
AS
VALUE3 ,
???? NAME
AS
NAME4,
VALUE
?
AS
VALUE4
FROM
T
WHERE
ROWID =4
) TT
GROUP
?
BY
NO
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
