27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #T_A(
EQUIP_DEPART_ID int
, EQUIP_NAME varchar(200)
)
INSERT INTO #T_A
SELECT 1,N'设备A'
UNION all
SELECT 2,N'设备B'
UNION all
SELECT 2,N'设备C'
UNION all
SELECT 2,N'设备D'
UNION all
SELECT 1,N'设备D'
UNION all
SELECT 1,N'设备D'
UNION all
SELECT 1,N'设备D'
UNION all
SELECT 3,N'设备A'
UNION all
SELECT 3,N'设备A'
UNION all
SELECT 4,N'设备C'
UNION all
SELECT 4,N'设备D'
UNION all
SELECT 4,N'设备A'
--select * from #T_A
declare @sql varchar(max)
set @sql=''
select @sql=@sql + ',['+rtrim(EQUIP_NAME)+']=sum(case EQUIP_NAME when '''+rtrim(EQUIP_NAME)+''' then 1 else 0 end)'
from #T_A group by EQUIP_NAME
exec('select EQUIP_DEPART_ID'+@sql+'from #T_A group by EQUIP_DEPART_ID' )
DROP TABLE #T_A
/*
(12 行受影响)
EQUIP_DEPART_ID 设备A 设备B 设备C 设备D
--------------- ----------- ----------- ----------- -----------
1 1 0 0 3
2 0 1 1 1
3 2 0 0 0
4 1 0 1 1
(4 行受影响)
*/
测试数据弄错了~您试试这个。
CREATE TABLE #T_A(
EQUIP_DEPART_ID int
, EQUIP_NAME varchar(200)
)
INSERT INTO #T_A
SELECT 1,N'设备A'
UNION
SELECT 2,N'设备B'
UNION
SELECT 2,N'设备C'
UNION
SELECT 2,N'设备D'
UNION
SELECT 1,N'设备D'
UNION
SELECT 1,N'设备D'
UNION
SELECT 1,N'设备D'
UNION
SELECT 3,N'设备A'
UNION
SELECT 3,N'设备A'
UNION
SELECT 4,N'设备C'
UNION
SELECT 4,N'设备D'
UNION
SELECT 4,N'设备A'
--select * from #T_A
declare @sql varchar(max)
set @sql=''
select @sql=@sql + ',['+rtrim(EQUIP_NAME)+']=sum(case EQUIP_NAME when '''+rtrim(EQUIP_NAME)+''' then 1 else 0 end)'
from #T_A group by EQUIP_NAME
exec('select EQUIP_DEPART_ID'+@sql+'from #T_A group by EQUIP_DEPART_ID' )
DROP TABLE #T_A
/*
(9 行受影响)
EQUIP_DEPART_ID 设备A 设备B 设备C 设备D
--------------- ----------- ----------- ----------- -----------
1 1 0 0 1
2 0 1 1 1
3 1 0 0 0
4 1 0 1 1
(4 行受影响)
*/