Looking for any solution to convert columns to rows.

I have my columns as [CA_resp],[CA_spen],[TX_resp],[TX_spen],[PA_resp],[PA_spen],these columns need to be converted to rows. all the states (CA,TX,PA) should go into [market] column,[resp] need to be other column [spen] need to be other column.

Here is an example: I have a table with the following schema:

 CREATE TABLE stage.test(
         id int null,
        [month] [varchar](255) NULL, 
        [year] [float] NULL,
        CA_resp [float] NULL,
        CA_spen [money] NULL,
        TX_resp [float] NULL, 
        TX_spen [money] NULL,
        PA_resp [float] NULL,   
        PA_spen [money] NULL)
    
    INSERT into stage.test
    values(1,    'jan', 2022,222,`enter code here`450,111,450,444,550)
    

I want my output to be.

    ID, month,year, market, resp ,spen
    1    jan    2022    CA  222     350
    1    jan    2022    TX  111     450
    1    jan    2022    PA  444     550

I tried using Pivot and didn't work.

CodePudding user response：

You can simply use 3 queries with UNION ALL. This approach is very efficient because we output 3 simple queries one after the other. There is no join or other comparison of data. (A join with UNION without all is a different story because it compares the queries to eliminate duplicates.)

     CREATE TABLE stagetest(
              id int null,
             [month] [varchar](255) NULL, 
             [year] [float] NULL,
             CA_resp [float] NULL,
             CA_spen [money] NULL,
             TX_resp [float] NULL, 
             TX_spen [money] NULL,
             PA_resp [float] NULL,   
             PA_spen [money] NULL);
         
         INSERT into stagetest
         values(1,    'jan', 2022,222,450,111,450,444,550);
     --ID, month,year, market, resp ,spen
      --   1    jan    2022    CA  222     350
     --    1    jan    2022    TX  111     450
     --    1    jan    2022    PA  444
     SELECT id, [month], [year], 'CA' market, ca_resp Resp, ca_spen Spen
     from stagetest
     union all
     SELECT id, [month], [year], 'PA', pa_resp, pa_spen
     from stagetest
     union all
     SELECT id, [month], [year], 'TX', tx_resp, tx_spen
     from stagetest;

fiddle here

CodePudding user response：

You cannot use Pivot in your case, since you don't have enough rows/columns.
You need to "create" rows, which can be done using seperate select statements combined with union.
I prefer to cross join them so you don't have to repeat the same columns in every select.
But you can also use the select with union direct, it's up to you

select t.id, t.month, t.year, v.market, v.CA_resp as resp, v.CA_spen as spen
from   test t
  cross join (select 'CA' as market, CA_resp, CA_spen from test
              union
              select 'TX' as market, TX_resp, TX_spen from test
              union
              select 'PA' as market, PA_resp, PA_spen from test
             ) v

The result is

Here is the DBFiddle

Or you could just put all fields in 3 selects with union, the result is the same just use what you like best

select id, month, year, 'CA' as market, CA_resp as resp, CA_spen as spen from test
union
select id, month, year, 'TX' as market, TX_resp, TX_spen from test
union
select id, month, year, 'PA' as market, PA_resp, PA_spen from test