0

I am trying to convert my rows into columns, or my columns into rows... I am a little confused with which it is exactly but here's what I would want it to look like

Original table:

Month | Price
1       500  
2       600
3       700

what it needs to look like:

 1     2     3
500   600   700

Could anyone tell me how his could be done?

EDIT:

CREATE table #yourtable     (
   [Id] int, 
   [Value] varchar(6), 
   [ColumnName] varchar(13)) ;      
INSERT INTO #yourtable     (
   [Id], 
   [Value], 
   [ColumnName]) 
VALUES     
   (1, '1', 'Month'),     
   (2, '500', 'Price') ;   

select 
   Month, 
   Price 
from (   
   select 
      value, 
      columnname   
   from #yourtable ) d 
pivot 
  (max(value)   for columnname in (Month, Price) ) piv;
Luuk
  • 12,245
  • 5
  • 22
  • 33
Kibofigs
  • 15
  • 6
  • "I am trying to convert my rows into columns" - please show us what you have tried. – Dale K Sep 13 '22 at 08:54
  • Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – Thom A Sep 13 '22 at 08:59
  • `CREATE table #yourtable ([Id] int, [Value] varchar(6), [ColumnName] varchar(13)) ; INSERT INTO #yourtable ([Id], [Value], [ColumnName]) VALUES (1, '1', 'Month'), (2, '500', 'Price') ; select Month, Price from ( select value, columnname from #yourtable ) d pivot ( max(value) for columnname in (Month, Price) ) piv;` I have tried this but this just gives it to me rows as the original – Kibofigs Sep 13 '22 at 09:00
  • Try to add some info to your question, like answers to: 1) Can there be multiple records for a month? What is the maximum number of records? 3) What should happen when there are records with month=`12,1,2` (a year break) ? – Luuk Sep 13 '22 at 09:00
  • @Larnu, It does not :( is there a way to replace the headers with the month? I apologize for being a noob, I dont understand – Kibofigs Sep 13 '22 at 09:01
  • The column name *would* be the month with that solution, @Kibofigs , as you would have `Month IN ([1], [2], [3])`. `[1], [2], [3]` are your column names. – Thom A Sep 13 '22 at 09:02
  • 1
    @Kibofigs: comments which contain code should be added to your question using [edit]. – Luuk Sep 13 '22 at 09:03
  • @Luuk, I'm sorry... I did not know – Kibofigs Sep 13 '22 at 09:05
  • You are probably looking for `Pivot` and `Unpivot`. Check [this](https://www.geeksforgeeks.org/pivot-and-unpivot-in-sql/) out. – CodeJunkie Sep 13 '22 at 09:07
  • Try to repeat the example give in the docs: [FROM - Using PIVOT (and UNPIVOT)](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16) – Luuk Sep 13 '22 at 09:12
  • Your DDL and sample data table aren't the same; which is correct? – Thom A Sep 13 '22 at 09:13

1 Answers1

0

You wrote an almost correct query.

select 
    Month, 
   Price 
from (   
   select 
      value, 
      columnname   
   from #yourtable) d 
pivot 
  (max(value)   for columnname in ('Month' AS Month, 'Price' AS Price) ) piv;
viking
  • 260
  • 2
  • 10