v代表某值, id为主键只转换一半怎么搞呢?把v1、v2、v3做行列转换,前面的id、 name、phone不变。如下:table
id name phone v1 v2 v3
1 张三 123456 111 222 333
2 李四 123123 444 555 666
3 王五 121212 777 888 999
4...
...行列转换为:
id name phone v1
1 张三 123456 111
222
333
2 李四 123123 444
555
666
3 王五 121212 777
888
999
4...
...
id name phone v1 v2 v3
1 张三 123456 111 222 333
2 李四 123123 444 555 666
3 王五 121212 777 888 999
4...
...行列转换为:
id name phone v1
1 张三 123456 111
222
333
2 李四 123123 444
555
666
3 王五 121212 777
888
999
4...
...
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-20 09:57:26
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id int,name varchar(4),phone int,v1 int,v2 int,v3 int)
INSERT INTO @tb
SELECT 1,'张三',123456,111,222,333 UNION ALL
SELECT 2,'李四',123123,444,555,666 UNION ALL
SELECT 3,'王五',121212,777,888,999--SQL查询如下:SELECT id,name,phone,v1
FROM (
SELECT RTRIM(id) AS id,name,RTRIM(phone) AS phone,v1,flag = id,flag2 = 0 FROM @tb
UNION ALL
SELECT '','','',v2,flag = id,flag2 = 1 FROM @tb
UNION ALL
SELECT '','','',v1,flag = id,flag2 = 2 FROM @tb
) AS T
ORDER BY flag,flag2/*
id name phone v1
------------ ---- ------------ -----------
1 张三 123456 111
222
111
2 李四 123123 444
555
444
3 王五 121212 777
888
777(9 行受影响)*/
UNION ALL
SELECT '','','',V2 FROM TB
UNION ALL
SELECT '','','',V3 FROM TB
UNION ALL
SELECT '','','',V4 FROM TB
(select id,name,phone,v1 from table
union all
select id,name,phone,v2 from table
union all
select id,name,phone,v3 from table) t
order by id,name,phone
就是v1,v2,v3后面各多了一个reason,用r1,r2,r3表示的,
table
id name phone v1 r1 v2 r2 v3 r3
1 张三 123456 111 abc 222 jkl 333 stu
2 李四 123123 444 def 555 mno 666 vwx
3 王五 121212 777 ghi 888 pqr 999 yz
4...
...
行列转换为:
id name phone v1
1 张三 123456 111 abc
222 def
333 ghi
2 李四 123123 444 jkl
555 mno
666 pqr
3 王五 121212 777 stu
888 vwx
999 yz
4...
...
我用不用再发个帖子呢?
id name phone v1 r1
1 张三 123456 111 abc
222 def
333 ghi
2 李四 123123 444 jkl
555 mno
666 pqr
3 王五 121212 777 stu
888 vwx
999 yz
4...
...
INSERT INTO @tb
SELECT 1,'张三',123456,111,'aaa',222,'ddd',333,'ggg' UNION ALL
SELECT 2,'李四',123123,444,'bbb',555,'eee',666,'hhh' UNION ALL
SELECT 3,'王五',121212,777,'ccc',888,'fff',999,'iii'SELECT id,name,phone,v1,r1
FROM (
SELECT RTRIM(id) AS id,name,RTRIM(phone) AS phone,v1,RTRIM(r1) AS r1 ,flag = id,flag2 = 0 ,flag3=0 FROM @tb UNION ALL
SELECT '','','',v2,r2,flag = id,flag2 = 1,flag3=1 FROM @tb
union all
select '','','',v3,r3,flag = id,flag2 = 2 ,flag3=2 from @tb
) AS T
ORDER BY flag,flag2,flag3结果:
(所影响的行数为 3 行)id name phone v1 r1
1 张三 123456 111 aaa
222 ddd
333 ggg
2 李四 123123 444 bbb
555 eee
666 hhh
3 王五 121212 777 ccc
888 fff
999 iii (所影响的行数为 9 行)