Question

sql多列合并为一列多行

如一个表有列id(1,2,3),a,b,c,d
查出来显示的是id s
1 a
b
c
d

2 a
b
c
d

Answer 1

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

Answer 2

行专列
语句比较麻烦
用的是decode + union

追问

有具体的sql语句吗