Pivot two columns based on a value without aggregations

Question

I tried to search before asking, but I didn't find something similar to the one I try to figure out. I use sql server to achieve that.

Current Situation

Target Based on the Year, I want to pivot:

Col as the name of a new column
value should be the value of the column.

In that example, the first 36 rows should become one row. For every year there should be one row.

A  B C   D YEAR E F HiBioInsec HiChemInsec etc
76 1 191 4 2020       5000        2000
76 1 191 4 2021       5000        2000

I tried with pivot and max but I didn't got the expected output. Any thoughts?

Pivoting *is* a form of aggregation, you can't pivot without it. — Thom A, Sep 08 '21 at 12:26
Does this answer your question? [TSQL Pivot without aggregate function](https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Thom A, Sep 08 '21 at 12:27
Can you add the table and data scripts for moderators to try and provide solutions please? — Gudwlk, Sep 08 '21 at 12:38
@Larnu I can try your second comment, but I will have to recreate 36 cases... (Which already did once, in order to concatenate 3 columns to one) — Proffesore, Sep 08 '21 at 12:38
If you want 36 columns, then yes, you need to define 36 columns, @Proffesore . I don't know what else you would be expecting there. — Thom A, Sep 08 '21 at 12:42
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, Sep 08 '21 at 12:46
@Larnu yes! the second comment answers my question. At a later stage, I will have another column like year. I guess adding that to the Group By clause will give me the correct result. — Proffesore, Sep 08 '21 at 13:02

John.Poston · Answer 1 · 2021-09-08T13:04:40.193

PIVOT seems like exactly what you need, actually. Does yours look like this at all? This worked for me.

CREATE TABLE dbo.YourTable
(
  A int,
  B int,
  C int,
  D int,
  [Year] int,
  E int,
  F int,
  col varchar(30),
  [value] int
);

INSERT dbo.YourTable (A,B,C,D,[Year],col, [value]) 
VALUES (76, 1, 191, 4, 2020, 'HiBioInsec', 5000);

INSERT dbo.YourTable (A,B,C,D,[Year],col, [value]) 
VALUES (76, 1, 191, 4, 2020, 'HiChemInsec', 2000);

INSERT dbo.YourTable (A,B,C,D,[Year],col, [value]) 
VALUES (76, 1, 191, 4, 2021, 'HiBioInsec', 5000);

INSERT dbo.YourTable (A,B,C,D,[Year],col, [value]) 
VALUES (76, 1, 191, 4, 2021, 'HiChemInsec', 2000);

SELECT A,B,C,D,[Year],E,F
    , pvt.HiBioInsec
    , pvt.HiChemInsec
FROM
(
   SELECT A,B,C,D,[Year],E,F,col, SUM([value]) AS SumValue
   FROM dbo.YourTable [tbl]
   GROUP BY A,B,C,D,[Year],E,F,col
) src
PIVOT (SUM(SumValue) FOR col IN ([HiBioInsec],[HiChemInsec])) pvt

Can you post your SQL?

You could also try something like this, but I don't see how you can get around using an aggregate.

SELECT A,B,C,D,[Year],E,F
, SUM(HiBioInsec) AS HiBioInsec
,SUM(HiChemInsec) AS HiChemInsec
FROM
(
SELECT A,B,C,D,[Year],E,F, [value] AS HibioInsec ,NULL AS HiChemInsec
FROM dbo.YourTable WHERE col = 'HiBioInsec'

UNION ALL

SELECT A,B,C,D,[Year],E,F, NULL AS HibioInsec , [value] AS HiChemInsec
FROM dbo.YourTable WHERE col = 'HiChemInsec'
) tbl
GROUP BY A,B,C,D,[Year],E,F

SUM will summarize the values of each row for the Year, which is not what I am looking. Also, SUM works with a value that makes sense to summarize. For me that value is a Year. Thank you for thinking with me — Proffesore, Sep 08 '21 at 12:57
https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function worked for me, @Larnu comment — Proffesore, Sep 08 '21 at 13:12

Pivot two columns based on a value without aggregations

1 Answers1