数据库如下:
Record 类型 值
1 1 100
2 2 200
3 1 200
4 3 100
5 2 200
6 4 240我想把值按类型分几列来显示结果如下:Record "1" "2" "3+4"
1 100
2 200
3 200
4 100
5 200
6 240
也就是1的为一列 2的为一列 3的和4的为合并在一列,,,这个SELECE怎么写,,,因我不是搞IT的,做个东西,自己用一下的,,,所以请大家帮个忙,谢谢!!!
Record 类型 值
1 1 100
2 2 200
3 1 200
4 3 100
5 2 200
6 4 240我想把值按类型分几列来显示结果如下:Record "1" "2" "3+4"
1 100
2 200
3 200
4 100
5 200
6 240
也就是1的为一列 2的为一列 3的和4的为合并在一列,,,这个SELECE怎么写,,,因我不是搞IT的,做个东西,自己用一下的,,,所以请大家帮个忙,谢谢!!!
declare @T table (Record int,类型 int,值 int)
insert into @T
select 1,1,100 union all
select 2,2,200 union all
select 3,1,200 union all
select 4,3,100 union all
select 5,2,200 union all
select 6,4,240SELECT
Record,
MAX(CASE WHEN 类型=1 THEN 值 END) AS [1],
MAX(CASE WHEN 类型=2 THEN 值 END) AS [2],
MAX(CASE WHEN 类型 IN(3,4) THEN 值 END) AS [3+4]
FROM @T
GROUP BY Record/*
Record 1 2 3+4
----------- ----------- ----------- -----------
1 100 NULL NULL
2 NULL 200 NULL
3 200 NULL NULL
4 NULL NULL 100
5 NULL 200 NULL
6 NULL NULL 240(所影响的行数为 6 行)
*/
insert @t select 1,1,100
insert @t select 2,2,200
insert @t select 3,1,200
insert @t select 4,3,100
insert @t select 5,2,200
insert @t select 6,4,240select
Record,
["1"]=case when (类型=1) then ltrim(值) else '' end,
["2"]=case when (类型=2) then ltrim(值) else '' end,
["3+4"]=case when 类型 in (3,4) then ltrim(值) else '' end
from @t
/*
Record "1" "2" "3+4"
----------- ------------ ------------ ------------
1 100
2 200
3 200
4 100
5 200
6 240
*/
declare @T table (Record int,类型 int,值 int)
insert into @T
select 1,1,100 union all
select 2,2,200 union all
select 3,1,200 union all
select 4,3,100 union all
select 5,2,200 union all
select 6,4,240SELECT
Record,
MAX(CASE WHEN 类型=1 THEN RTRIM(值) ELSE '' END) AS [1],
MAX(CASE WHEN 类型=2 THEN RTRIM(值) ELSE '' END) AS [2],
MAX(CASE WHEN 类型 IN(3,4) THEN RTRIM(值) ELSE '' END) AS [3+4]
FROM @T
GROUP BY Record/*
Record 1 2 3+4
----------- ------------ ------------ ------------
1 100
2 200
3 200
4 100
5 200
6 240(所影响的行数为 6 行)
*/