再问行转列,有点复杂

csshan 2009-08-07 12:22:03

id dir c_name value

625 18376_18022 N1 1_74
625 18376_18022 N1RXLEV 48
625 18376_18022 N2 15_70
625 18376_18022 N2RXLEV 33
625 18376_18022 N3 75_76
625 18376_18022 N3RXLEV 39
625 18376_18022 N4 76_
625 18376_18022 N4RXLEV 30
625 18376_18022 N5 77_71
625 18376_18022 N5RXLEV 32
625 18376_18022 N6 78_73
625 18376_18022 N6RXLEV 44

把N1RXLEV,N2RXLEV,N3RXLEV,..N6RXLEV行转列

结果

id dir c_name value RXLEX

625 18376_18022 N1 1_74 48

625 18376_18022 N2 15_70 33

625 18376_18022 N3 75_76 39

625 18376_18022 N5 77_71 32

625 18376_18022 N6 78_73 44

...全文

70 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

csdyyr 2009-08-07

打赏
举报

DECLARE @TB TABLE([id] INT, [dir] VARCHAR(11), [c_name] VARCHAR(7), [value] VARCHAR(5))

INSERT @TB 

SELECT 625, '18376_18022', 'N1', '1_74' UNION ALL 

SELECT 625, '18376_18022', 'N1RXLEV', '48' UNION ALL 

SELECT 625, '18376_18022', 'N2', '15_70' UNION ALL 

SELECT 625, '18376_18022', 'N2RXLEV', '33' UNION ALL 

SELECT 625, '18376_18022', 'N3', '75_76' UNION ALL 

SELECT 625, '18376_18022', 'N3RXLEV', '39' UNION ALL 

SELECT 625, '18376_18022', 'N4', '76_' UNION ALL 

SELECT 625, '18376_18022', 'N4RXLEV', '30' UNION ALL 

SELECT 625, '18376_18022', 'N5', '77_71' UNION ALL 

SELECT 625, '18376_18022', 'N5RXLEV', '32' UNION ALL 

SELECT 625, '18376_18022', 'N6', '78_73' UNION ALL 

SELECT 625, '18376_18022', 'N6RXLEV', '44'



SELECT id,dir,

	MIN(c_name) AS c_name,

	MAX(CASE WHEN  RIGHT(c_name,5)<>'RXLEV' THEN value END) AS value,

	MAX(CASE WHEN  RIGHT(c_name,5)='RXLEV' THEN value END) AS RXLEV

FROM @TB 

GROUP BY id,dir,LEFT(c_name,2)

/*

id          dir         c_name  value RXLEV 

----------- ----------- ------- ----- ----- 

625         18376_18022 N1      1_74  48

625         18376_18022 N2      15_70 33

625         18376_18022 N3      75_76 39

625         18376_18022 N4      76_   30

625         18376_18022 N5      77_71 32

625         18376_18022 N6      78_73 44

*/

csshan 2009-08-07

打赏
举报

谢谢

pt1314917 2009-08-07

打赏
举报



--结果：

(12 行受影响)

id          dir         c_name value RXLEX

----------- ----------- ------ ----- -----

625         18376_18022 N1     1_74  48

625         18376_18022 N2     15_70 33

625         18376_18022 N3     75_76 39

625         18376_18022 N4     76_   30

625         18376_18022 N5     77_71 32

625         18376_18022 N6     78_73 44

pt1314917 2009-08-07

打赏
举报

--> 测试数据: [tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb] (id int,dir varchar(11),c_name varchar(7),value varchar(5))

insert into [tb]

select 625,'18376_18022','N1','1_74' union all

select 625,'18376_18022','N1RXLEV','48' union all

select 625,'18376_18022','N2','15_70' union all

select 625,'18376_18022','N2RXLEV','33' union all

select 625,'18376_18022','N3','75_76' union all

select 625,'18376_18022','N3RXLEV','39' union all

select 625,'18376_18022','N4','76_' union all

select 625,'18376_18022','N4RXLEV','30' union all

select 625,'18376_18022','N5','77_71' union all

select 625,'18376_18022','N5RXLEV','32' union all

select 625,'18376_18022','N6','78_73' union all

select 625,'18376_18022','N6RXLEV','44'



select id,dir,c_name=left(c_name,2),

[value]=max(case when charindex('rxlev',c_name)>0 then '' else [value] end),

RXLEX=max(case when charindex('rxlev',c_name)>0 then [value] else '' end)

 from [tb]

group by id,dir,left(c_name,2)