原文:
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://images.cnitblog.com/blog/73542/201502/111435313234076.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
