-1

Most of the examples that I've seen are more complex, so I guess I posted something simple to fully understand the concept of pivot. 

declare @yt TABLE
(
  Id int, 
  Dept varchar(10),
  [Day] int,
  [Counter] int
);

INSERT INTO @yt
(
  ID, Dept, [Day], [Counter]
)
VALUES
    (102, 'DeptA', 20170704, 96),
    (102, 'DeptP', 20170704, 223),
    (103, 'DeptA', 20170704, 84),
    (103, 'DeptW', 20170704, 43);

select *
from 
(
  select id, dept, [day], [Counter]
  from @yt
) src
pivot
(
  sum([Counter])
  for Dept in ([DeptA], [DeptP], [DeptW])
) piv;

From my understanding, the code within Pivot does the following: 1) aggregates the column selected (sum([counter])), pivots the column in for Dept and then does a group by of the rest of the columns that are not specified (ID and Day).

Would I be able to add two sums? Or is this something beyond the scope of the pivot functionality? The following generates an error:

declare @yt TABLE
(
  Id int, 
  Dept varchar(10),
  [Day] int,
  [Counter] int,
  Sales int
);

INSERT INTO @yt
(
  ID, Dept, [Day], [Counter], Sales
)
VALUES
    (102, 'DeptA', 20170704, 96, 12),
    (102, 'DeptP', 20170704, 223, 5),
    (103, 'DeptA', 20170704, 84, 9),
    (103, 'DeptW', 20170704, 43, 11);

select *
from 
(
  select id, dept, [day], [Counter], sales
  from @yt
) src
pivot
(
  sum([Counter]), ([sales])
  for Dept in ([DeptA], [DeptP], [DeptW])
) piv;

Wanted to add that this question was helpful in understanding the pivot tables.

fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110
  • This concern is valid if your data can have more than one row per week-store. Can that happen?, what result would you want in that case? – Lamak Jul 07 '17 at 17:09
  • @Lamak true, but that would impact his "regular query" as well, so really it's a data issue - and not a pivot issue. Or am I misunderstanding? – Stan Shaw Jul 07 '17 at 17:11
  • @StanShaw No, that's right, but I'm just trying to understand a valid concern – Lamak Jul 07 '17 at 17:12
  • @Lamak Understood and agree. I think he should be fine, because based on the table structure, it doesn't seem logical that he would have multiple records from the same store for the same week - of course we don't know what 'XCount' really is. If that was possible, I would think he would want to sum those values together, in which case the query should be fine, regardless. But, again, I'm assuming - and could very easily be wrong. – Stan Shaw Jul 07 '17 at 17:16
  • I've changed the query to reflect more valid data. There will only be one row per ID, Dept, Day. – fdkgfosfskjdlsjdlkfsf Jul 07 '17 at 17:24
  • What do you mean when you say "it's using an aggregate that I would not normally use in the regular query"? – Stan Shaw Jul 07 '17 at 17:27
  • It just worries me a bit that, in order to simply change the format of the data so it fits a reporting tool, I need to add an aggregate function, in this case the `sum`. – fdkgfosfskjdlsjdlkfsf Jul 07 '17 at 17:32
  • @rbhat I still don't understand what you mean - sorry. If you could clarify with examples of using - and not using - `SUM` and how you think your output would be the same, I might understand. But, it seems like the grouping you're doing isn't actually consolidating any rows - and you think that makes the grouping superfluous - which to a degree is true - however, aggregate functions require grouping/pivoting, so you don't have a choice. – Stan Shaw Jul 10 '17 at 11:44

1 Answers1

0

You can always prevent the aggregate operation by insuring the rows are unique. Although an aggregation {SUM|MIN|MAX} must still be present in the syntax, if the rows are unique, it can't ever actually have multiple rows to operate upon.

A bigger question is what you want to have happen if there ARE multiple rows. In the case below I duplicated 1 source row and you'll see how it appears by running the code. If you want to ignore additional rows you can just add "where r = 1". 

declare @yt TABLE
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

INSERT INTO @yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

select piv.Store ,
       piv.[1] ,
       piv.[2] ,
       piv.[3]
from 
(
  select store, week, xCount,row_number() over(partition by Store,Week order by Store) R
  from @yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;
bielawski
  • 1,466
  • 15
  • 20