Pivot data T-SQL

Question

I have the following table:

I want to pivot it to the following table:

I tried to work with the following example: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ But in this case a SUM operator is mandatory. I don't want to use the SUM operator for my data.

What would be the best approach to go. Eventually I want to use the data again, I want to prevent that I need to make a lot of "left join" statements like the following:

select table_a.Cat,one.number as number1, one.namevalue as Name1, two.number as number2, two.namevalue as name2
from table_a 
left outer join (
    select *
    from #temp
    where numbercat = 'number1'
) as one on table_a.cat = one.cat
left outer join (
    select *
    from #temp
    where numbercat = 'number2'
) as two on table_a.cat = two.cat

I am able to unpivot the numbercat & number with the following code:

select *
from 
(
  select cat, numbercat, number
  from #input
) src
pivot
(
  min(number)
  for numbercat in ([1], [2], [3])
) piv;

What should I do to also incorporate the Namecat & namevalue data?

Use MIN or MAX then. Or `STRING_AGG`. You have to use *something*. The server can't decide by itself which of the multiple possible values to return in each column. Should it return `NULL`? `Paul`? What if there are 3 or four names? — Panagiotis Kanavos, Sep 20 '21 at 09:27
To pivot you need to aggregate, pivoting is a form of aggregation. I must also admit, it seems odd that you are [unpivoting](https://stackoverflow.com/questions/69250827/unpivot-data-t-sql) your data only to pivot it back to again. — Thom A, Sep 20 '21 at 09:28
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Thom A, Sep 20 '21 at 09:29
@larnu, I unpivot it and then I order the data, so for CAT B, first number becomes 1 and second number becomes 20. Then I want to pivot it again. — Zayfaya83, Sep 20 '21 at 09:32
Apart from that you have replaced the empty string values with `NULL`, @Zayfaya83 , I see no difference in your [original data](https://i.stack.imgur.com/XdAMI.png) in your original question and the [expected results](https://i.stack.imgur.com/RJacF.png) in this question. And replacing `''` with `NULL` is trivial: use `NULLIF`. — Thom A, Sep 20 '21 at 09:36
I think the data adjustment I do after unpivot is irrelevant for this question. — Zayfaya83, Sep 20 '21 at 09:40
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Sep 20 '21 at 10:16

score 1 · Accepted Answer · answered Sep 20 '21 at 10:17

I find that conditional aggregation is usually simpler and more versatile than pivot:

select cat,
       max(case when numbercat = 'number1' then number end) as number1,
       max(case when numbercat = 'number2' then number end) as number2,
       max(case when namecat = 'name1' then name end) as name1,
       max(case when namecat = 'name2' then name end) as name2
from #temp
group by cat;

Pivot data T-SQL

1 Answers1