问SQL如何实现列转行的问题
数据是这样的：
001 张三 老板
002 李四 员工
003 王五 主管
执行后的结果是
001
张三
老板
002
李四
员工
003
王五
主管
[解决办法]
这样吗：
create table tb(a varchar(20),b varchar(20),c varchar(20))
insert Into tb
select '001', '张三', '老板' union all
select '002', '李四', '员工' union all
select '003', '王五', '主管'
select v
from
(
select A as v ,A as ord,1 col_ord from tb
union all
select B,A,2 col_ord from tb
union all
select C,A,3 col_ord from tb
)t
order by ord,col_ord
/*
v
001
张三
老板
002
李四
员工
003
王五
主管
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-11 12:53:35
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([a] varchar(3),[b] varchar(4),[c] varchar(4))
insert [huang]
select '001','张三','老板' union all
select '002','李四','员工' union all
select '003','王五','主管'
--------------开始查询--------------------------
SELECT [NewValue]
FROM ( SELECT CASE WHEN id IN ( 1, 2, 3 ) THEN [a]
END [NewValue] ,
id
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY a ) id
FROM huang
) huang
UNION ALL
SELECT CASE WHEN id IN ( 1, 2, 3 ) THEN b
END ,
id
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY a ) id
FROM huang
) huang
UNION ALL
SELECT CASE WHEN id IN ( 1, 2, 3 ) THEN C
END ,
id
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY a ) id
FROM huang
) huang
) b
ORDER BY id ,ASCII(NewValue)
----------------结果----------------------------
/*
NewValue
--------
001
老板
张三
002
李四
员工
003
王五
主管
*/
