组合结果
我需要将结果显示在一行而不是少数。
查询:
SELECT C.ClientCaseNumber,
C.SetId,
Sum(CASE
WHEN CA.CaseActionDefinitionId IN (28, 29, 30) THEN 1
ELSE 0
END) AS [Wezwania],
Sum(CASE
WHEN CA.CaseActionDefinitionId IN (14, 21) THEN 1
ELSE 0
END) AS [Kontakt],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 32 THEN 1
ELSE 0
END) AS [SMS],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 44 THEN 1
ELSE 0
END) AS [Zgon],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 49 THEN 1
ELSE 0
END) AS [Areszt],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 37 THEN 1
ELSE 0
END) AS [Odmowa],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 39 THEN 1
ELSE 0
END) AS [Podważa],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 99 THEN 1
ELSE 0
END) AS [Ugoda],
[Adres],
[Info1],
[Numer],
[Info2],
[Mail],
[Info3],
[Powód]
FROM (SELECT cast(Notes as char) AS [Adres], CaseActionDefinitionId AS [Info1], NULL AS [Numer], NULL as [Info2], NULL AS [Mail], NULL as [Info3], NULL AS [Powód], CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId = 68
UNION ALL
SELECT NULL, NULL, cast(Info as char), CaseActionDefinitionId, NULL, NULL, NULL, CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId IN (54, 55, 56, 58,
59, 60, 61, 62, 63)
UNION ALL
SELECT NULL, NULL, NULL, NULL, cast(Notes as char), CaseActionDefinitionId, NULL, CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId = 66
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, cast(Description as char(100)), CaseDetailId
FROM CaseActionDefinition JOIN CaseActionHistory AS C ON DefinitionId = C.CaseActionDefinitionId
WHERE DefinitionId BETWEEN 70 AND 78) AS x
INNER JOIN CaseDetails AS C
ON x.CaseDetailId = C.CaseDetailsId
INNER JOIN CaseActionHistory AS CA
ON C.CaseDetailsId = CA.CaseDetailId
WHERE C.ClientId = 11
GROUP BY C.ClientCaseNumber, [Adres], [Numer], [Mail], [Info1], [Powód], [Info2], [Info3], C.SetId
我想我需要在GROUP某种程度上摆脱几件事情BY,但是我真的没有一个想法如何更改查询本身做正确事情的工作。
我认为你应该声明表并插入它:SetId和所有SUM(...),并通过SetId组。然后用其他数据更新表格。
样品:
declare @table table (
ClientCaseNumber int
,SetId int
,Wezwania int
,Kontakt int
,SMS int
,Zgon int
,Areszt int
,Odmowa int
,Podważa int
,Ugoda int
,Adres nvarchar(255)
,Info1 nvarchar(255)
,Numer nvarchar(255)
,Info2 nvarchar(255)
,Mail nvarchar(255)
,Info3 nvarchar(255)
,Powód nvarchar(255))
insert into @table (
ClientCaseNumber
,Wezwania
,Kontakt
,SMS
,Zgon
,Areszt
,Odmowa
,Podważa
,Ugoda)
SELECT
C.ClientCaseNumber,
Sum(CASE
WHEN CA.CaseActionDefinitionId IN (28, 29, 30) THEN 1
ELSE 0
END) AS [Wezwania],
Sum(CASE
WHEN CA.CaseActionDefinitionId IN (14, 21) THEN 1
ELSE 0
END) AS [Kontakt],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 32 THEN 1
ELSE 0
END) AS [SMS],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 44 THEN 1
ELSE 0
END) AS [Zgon],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 49 THEN 1
ELSE 0
END) AS [Areszt],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 37 THEN 1
ELSE 0
END) AS [Odmowa],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 39 THEN 1
ELSE 0
END) AS [Podważa],
Sum(CASE
WHEN CA.CaseActionDefinitionId = 99 THEN 1
ELSE 0
END) AS [Ugoda]
FROM (SELECT cast(Notes as char) AS [Adres], CaseActionDefinitionId AS [Info1], NULL AS [Numer], NULL as [Info2], NULL AS [Mail], NULL as [Info3], NULL AS [Powód], CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId = 68
UNION ALL
SELECT NULL, NULL, cast(Info as char), CaseActionDefinitionId, NULL, NULL, NULL, CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId IN (54, 55, 56, 58,
59, 60, 61, 62, 63)
UNION ALL
SELECT NULL, NULL, NULL, NULL, cast(Notes as char), CaseActionDefinitionId, NULL, CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId = 66
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, cast(Description as char(100)), CaseDetailId
FROM CaseActionDefinition JOIN CaseActionHistory AS C ON DefinitionId = C.CaseActionDefinitionId
WHERE DefinitionId BETWEEN 70 AND 78) AS x
INNER JOIN CaseDetails AS C
ON x.CaseDetailId = C.CaseDetailsId
INNER JOIN CaseActionHistory AS CA
ON C.CaseDetailsId = CA.CaseDetailId
WHERE C.ClientId = 11
GROUP BY C.ClientCaseNumber
update @table
set SetId = X.SetId
, Adres = X.Adres
,Info1 = X.Info1
,Numer = X.Numer
,Info2 = X.Info2
,Mail = X.Mail
,Info3 = X.Info3
,Powód = X.Powód
from
@table as T
join (
SELECT
C.ClientId,
C.SetId,
[Adres],
[Info1],
[Numer],
[Info2],
[Mail],
[Info3],
[Powód]
FROM (SELECT cast(Notes as char) AS [Adres], CaseActionDefinitionId AS [Info1], NULL AS [Numer], NULL as [Info2], NULL AS [Mail], NULL as [Info3], NULL AS [Powód], CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId = 68
UNION ALL
SELECT NULL, NULL, cast(Info as char), CaseActionDefinitionId, NULL, NULL, NULL, CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId IN (54, 55, 56, 58,
59, 60, 61, 62, 63)
UNION ALL
SELECT NULL, NULL, NULL, NULL, cast(Notes as char), CaseActionDefinitionId, NULL, CaseDetailId
FROM CaseActionHistory
WHERE CaseActionDefinitionId = 66
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, cast(Description as char(100)), CaseDetailId
FROM CaseActionDefinition JOIN CaseActionHistory AS C ON DefinitionId = C.CaseActionDefinitionId
WHERE DefinitionId BETWEEN 70 AND 78) AS x
INNER JOIN CaseDetails AS C
ON x.CaseDetailId = C.CaseDetailsId
INNER JOIN CaseActionHistory AS CA
ON C.CaseDetailsId = CA.CaseDetailId
WHERE
X.ClientId = 11
) as X on X.ClientCaseNumber = T.ClientCaseNumber
SELECT DISTINCT ClientCaseNumber,
SetId,
[Wezwania],
[Kontakt],
[SMS],
[Zgon],
[Areszt],
[Odmowa],
[Podważa],
[Ugoda],
[Adres],
[Info1],
[Numer],
[Info2],
[Mail],
[Info3],
[Powód]
FROM @table
然后再用我需要的数据进行另一个SELECT?导致上面报告生成器不会给我任何类型的字段来使用 – glaeran
然后从该表中选择所有结果。 样品: 选择 \t ClientCaseNumber \t,SETID \t,Wezwania \t,KONTAKT \t,短信 \t,Zgon \t,Areszt \t,Odmowa \t,Podważa \t,Ugoda \t,住址 \t,Info1 \t,NUMER \t,信息2 \t,邮件 \t,INFO3 \t,Powód 从 \t @table – Justas
因为我需要通过ClientCaseNumber分组不SETID我代替他们在表中。 http://pastebin.com/PdYGT1Z9 - 查询的当前外观。对所有结果显示相同的[数字] [邮件],[地址],[电源]。 – glaeran
,你能否告诉我们报表的设计视图?这个查询对于我来说看起来太大了,但是您应该能够在报告中对f.ex ClientCaseNumber进行分组。 –
报表设计器视图不过是一个带有插入列的表格。我不想按ClientCaseNumber进行分组。我需要将这3行合并为1. – glaeran