让结果横向输出,且nvcGroupName 字段取消重复
参考代码:
SELECT DISTINCT [D].[nvcID],[D].[nvcCarName]
,[A].[nvcGroupName]
,COUNT([B].[nvcName]) AS [nvcName]
FROM [dbo].[FITGroup] A
,[dbo].[FITGroupPeople] B
,[dbo].[FITCarDetail] C
,[dbo].[FITCar] D
WHERE [D].[nvcCarName] = '1车' AND
[D].[nvcID] = [C].[nvcFITCarID] AND
[C].[nvcFITGroupInfoID] = [A].[nvcID] AND
[A].[nvcID] = [B].[nvcFITGroupID]
GROUP BY [D].[nvcID],[D].[nvcCarName],[A].[nvcGroupName]
参考代码:
SELECT DISTINCT [D].[nvcID],[D].[nvcCarName]
,[A].[nvcGroupName]
,COUNT([B].[nvcName]) AS [nvcName]
FROM [dbo].[FITGroup] A
,[dbo].[FITGroupPeople] B
,[dbo].[FITCarDetail] C
,[dbo].[FITCar] D
WHERE [D].[nvcCarName] = '1车' AND
[D].[nvcID] = [C].[nvcFITCarID] AND
[C].[nvcFITGroupInfoID] = [A].[nvcID] AND
[A].[nvcID] = [B].[nvcFITGroupID]
GROUP BY [D].[nvcID],[D].[nvcCarName],[A].[nvcGroupName]
不行 把GUID 字段砍了,能行不
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(nvcgroupname)
+ '=max(case when [nvcgroupname]=' + QUOTENAME(nvcgroupname, '''')
+ ' then [nvcname] else 0 end)'
FROM tb
GROUP BY nvcid ,
nvccarname ,
nvcgroupname
EXEC('select nvcid,nvccarname'+@s+' from tb group by nvcid,nvccarname')
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(nvcgroupname)
+ '=max(case when [nvcgroupname]=' + QUOTENAME(nvcgroupname, '''')
+ ' then [nvcname] else ''0'' end)'
FROM tb
GROUP BY nvcid ,
nvccarname ,
nvcgroupname
EXEC('select nvcid,nvccarname'+@s+' from tb group by nvcid,nvccarname')
晕,你图片的nvcname只有数值而已。用这个试试