CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
justromagod
justromagod

Reputation: 993

Why I get 4 rows in Pivot?

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:

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?

Upvotes: 2

Views: 137

Answers (3)

gotqn
gotqn

Reputation: 43646

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;

Upvotes: 1

Sentinel
Sentinel

Reputation: 6449

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;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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.

Upvotes: 0

Related Questions