I have this to create headers and count from a SQL Column.
SELECT
Description, [DIC] as DIC, [DOC] as DOC, [CMF] as CMF,
DIC + DOC + CMF as Total
FROM
(
SELECT
Description,
TagName,
Area
FROM
v_AlarmHistory
WHERE
(EventStamp > :FechaInicio)
and
(EventStamp < :FechaFin)
and
Area <> 'Events'
and
AlarmState LIKE :Alarm
)t
PIVOT(
COUNT(TagName)
FOR Area IN(
[DIC],
[DOC],
[CMF],
[Total])
) AS pvt
ORDER BY Total DESC
But I want totalize all columns created after PIVOT instruction
The result after the PIVOT instruction is this
Good to see you got your PIVOT working 
It’s always good, especially with SQL questions, to give the table you have (which you’ve done) and also the table or resulting dataset that you’d like to see so that we’re not left guessing or assuming what you mean.
Are you wanting to summate the Totals column to give one total? E.g. 279+134+107+… = 123456
1 Like
When you paste in code, be sure to add three backticks (`) before and after your code to preserve the formatting. I took the liberty of editing your post to make it more readable.
2 Likes
I want to summate all the columns and get total of each one.
Try adding an aggregation to your DIC, DOC, and CMF columns, but grouping by (with Rollup) on your description.
It wont actually group anything further together because all of your descriptions are unique after the pivot, but will add an extra row that shows the totals and a null description
SELECT
Description, SUM([DIC]) as DIC, SUM([DOC]) as DOC, SUM([CMF]) as CMF,
SUM(DIC + DOC + CMF) as Total
FROM
(
SELECT
Description,
TagName,
Area
FROM
v_AlarmHistory
WHERE
(EventStamp > :FechaInicio)
and
(EventStamp < :FechaFin)
and
Area <> 'Events'
and
AlarmState LIKE :Alarm
)t
PIVOT(
COUNT(TagName)
FOR Area IN(
[DIC],
[DOC],
[CMF],
[Total])
) AS pvt
GROUP BY ROLLUP (Description)
ORDER BY Total DESC
If you want the total row to show “Totals” or something for the description, then add an Isnull around your selection of the Description column like this
ISNULL(Description, 'Totals') Description
1 Like
