0

First of all I am sorry but I do not even know how to search what I want so I try it with this question.

I have a table containing some data like this:

Id Number Periode Value
01 1 2,2
02 1 3,4
03 1 3,8
01 2 1,9
02 2 8,1
03 2 2,6

What I want is a resultset like this:

Id Number Periode 1 Periode 2
01 2,2 1,9
02 3,4 8,1
03 3,8 2,6

In the first table I have all my values for all rows in the same structure in the second table I have this stracture lets say "transposed" so each Periode gets there own column with the Value. I hope you can understand what I like to do here.

The amount of periods is variable in the best case. What I have is something like this, but this is not variable I need to adust it everytime the periods changes:

SELECT p.IDNumber,
       (SELECT Value FROM #table t where t.IDNumber = p.IDNumber and t.Periode = 1) Periode1,
       (SELECT Value FROM #table t where t.IDNumber = p.IDNumber and t.Periode = 2) Periode2,
       ...
FROM #table p

If you can just send me to the right SQL command I can use for something like this, would be sufficient but as I mentioned above I do not even know how to search for this kind of transformation (If this is even possible).

Thank you in Advance!

Bado
  • 71
  • 10
  • Since the periods are variable, I think this to be more complicated than you think. It's a classic use case for `PIVOT()` but you'll need to write dynamic SQL since `PIVOT()` requires a predefined list of values. Let me see if I can toss together something. – Edward Radcliffe Apr 20 '22 at 13:42
  • 1
    ^ this is _not_ as simple as "send me the right SQL command" - you're going to have to learn and do some work. [Start here](https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/?utm_source=AaronBertrand) and come back if you have any specific issues. – Aaron Bertrand Apr 20 '22 at 13:43
  • Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Apr 20 '22 at 13:44
  • Hi, yes thank you I think the PIVOT command is indeed the way to go here! I did not know that yet and this is of course enough to get me started with (Only know pivot from excel not from sql could have thought about that....). Thank you! If someone wants to be the accepted Answer maybe you can write one I will mark it as Answer than. – Bado Apr 20 '22 at 13:49

0 Answers0