Why I get 4 rows in Pivot?

Question

Pivoting Data

Pivoting is a technique that groups and aggregates data, transitioning it from a state of rows to a state of columns. In all pivot queries, you need to identify three elements:

What do you want to see on rows? This element is known as the on rows, or grouping element
What do you want to see on columns? This element is known as the on cols, or spreading element.
What do you want to see in the intersection of each distinct row and column value? This element is known as the data, or aggregation element.

pattern:

WITH PivotData AS
(
  SELECT
    < grouping column >,
    < spreading column >,
    < aggregation column >
  FROM < source table >
)
SELECT < select list >
FROM PivotData
  PIVOT( < aggregate function >(< aggregation column >)
    FOR < spreading column > IN (< distinct spreading values >) ) AS P;

I have this table created in SQL Server

CREATE TABLE [dbo].[NameValueData](
    [Name] [VARCHAR](50) NOT NULL,
    [Value] [INT] NOT NULL
) ON [PRIMARY]

and It has values

INSERT INTO NameValueData
VALUES
( 'N1', 1 ), 
( 'N2', 2 ), 
( 'N3', 3 ), 
( 'N4', 4 ),
--NOT FIXED Number of ROWS

And data is:

Name    Value
N1  1
N2  2
N3  3
N4  4
... ...

Now I need to rotate this Data and get results where columns names created based on row values from Column Name

N1  N2  N3  N4  ...
1   2   3   4   --Can be more

I tried to write my own Pivot SQL

WITH PivotData
AS (SELECT Value AS GroupingColumn,
           Name AS SpreadingColumn,
           Value AS AggregationColumn
    FROM dbo.NameValueData)
SELECT 1 AS Ignore,
       [N1],
       [N2],
       [N3],
       [N4]
FROM PivotData
    PIVOT
    (
        MAX(AggregationColumn)
        FOR SpreadingColumn IN ([N1], [N2], [N3], [N4])
    ) AS P;

Result is:

Ignore  N1  N2  N3  N4
1   1   NULL    NULL    NULL
1   NULL    2   NULL    NULL
1   NULL    NULL    3   NULL
1   NULL    NULL    NULL    4

Why I get 4 rows here?

Remove the groupingcolumn in the first select and you will see the result you want. The pivot opreator does an implicit `GROUP BY` for all non-referenced columns https://stackoverflow.com/a/53881131/4608204 — EzLo, Jun 06 '19 at 11:57

Sentinel · Accepted Answer · 2019-06-06T16:21:55.437

2

If you replace 1 AS Ignore in your select list with GroupingColumn you'll see why you are getting 4 records instead of one.

The PIVOT operation uses all of the grouping columns whether included in the final projection or not in an implied group by clause when performing the aggregation.

Removing GroupingColumn from the PivotData CTE and from the final projection will resolve your issue.

In fact due to the nature of your data you can completely get rid of the CTE and just use this query:

select * 
  from namevaluedata
 pivot (max(value)
        for name in ([N1], [N2], [N3], [N4])
       ) p;

edited Jun 06 '19 at 16:21

answered Jun 06 '19 at 16:15

Sentinel

6,379
1
18
23

It works, even I still don't get it how it works exactly – justromagod Jun 09 '19 at 12:36
It works because the `PIVOT` operator is a convenient shorthand for writing `select max(case name when 'N1' then value end) N1, max(case name when 'N2' then value end) N2, ... from namevaluedata group by ...` in your case you wanted a single group, so you can completely get rid of the grouping columns or ensure they have a static value, and then the `group by` can go away. – Sentinel Jun 10 '19 at 15:03

score 1 · Answer 2 · answered Jun 08 '19 at 08:06

1

Try this:

WITH PivotData
AS (SELECT Name AS SpreadingColumn,
           Value AS AggregationColumn
    FROM dbo.NameValueData)
SELECT 1 AS Ignore,
       [N1],
       [N2],
       [N3],
       [N4]
FROM PivotData
    PIVOT
    (
        MAX(AggregationColumn)
        FOR SpreadingColumn IN ([N1], [N2], [N3], [N4])
    ) AS P;

answered Jun 08 '19 at 08:06

gotqn

42,737
46
157
243

SQL works It works, but SQL from Sentinel more compact – justromagod Jun 09 '19 at 12:35

score 0 · Answer 3 · answered Jun 06 '19 at 11:47

0

I just don't like pivot and much prefer conditional aggregation. And this is one of the reasons.

The rows being generated are determined by all the values in the source data that are not in the PIVOT clause. Because you have a third column (GroupingColumn) it is used to define the rows.

answered Jun 06 '19 at 11:47

Gordon Linoff

1,242,037
58
646
786

yes, THEN WHEN looks much more self explanatory, but I don't know how to make it for my task. – justromagod Jun 09 '19 at 12:38

Why I get 4 rows in Pivot?

3 Answers3