Unpivot Data

Question

Unpivot Data

Bone_12 361

Hi,

I have a table where the data is formatted like below:

What I need is to unpivot (I think) the data so it's in the following format:

I essentailly need to then create a "Month" column which has Jan to Dec and then "Value" column which has the values captured within the columns Jan to Dec.

I haven't done this before and don't really know where to start with this or if it can even be done. Any help on this would be appreciated please?

Yitzhak Khabinsky 26,586 Reputation points

2022-10-27T18:47:00.24+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

4 answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2022-10-27T18:47:00.24+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

Answer 1

Viorel 122.6K

The UNPIVOT seems to work:

select remit, area, remit_type, area_type, cust_no, app_no, id, [month], [value], [year]  
from MyTable t  
unpivot  
(  
    [value] for [month] in (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, [Dec])  
) u  
order by remit, area, [year]

Answer 2

If just to clean up data, you can give Power BI Desktop a try. In SQL Server, you can unpivot your table with hard coded queries. Use unpivot, cross apply, or JSON.
Here is an example with JSON:
;with mycte (TheKey, TheValue) as (
SELECT [KEY], Value from OpenJson((
select * from youtable FOR JSON AUTO ))
WHERE type = 5)

 SELECT  
 --columns not in unpivot list  
 JSON_VALUE(src.TheValue,'$.remit') remit,  
 JSON_VALUE(src.TheValue,'$.area') area,  
  JSON_VALUE(src.TheValue,'$.remit_type') remit_type,  
 JSON_VALUE(src.TheValue,'$.area_type') area_type,  
 JSON_VALUE(src.TheValue,'$.cust_no') cust_no,  
 JSON_VALUE(src.TheValue,'$.app_no') app_no,  
 JSON_VALUE(src.TheValue,'$.id') id  
 ,unpvt.[Key] as [Month], unpvt.Value   
 , JSON_VALUE(src.TheValue,'$.year') [year]  
 FROM mycte AS src  
 CROSS APPLY OpenJson(src.TheValue) AS unpvt    
 WHERE unpvt.Type!=5  and  
 --columns not in unpivot list  
 unpvt.[Key] not in ('remit', 'area','remit_type','remit_type','cust_no','app_no','id','year')

Answer 3

Hi @Bone_12 ,

Try this code:

create table tempppp  
(  
remit varchar(10),  
area varchar(10),  
remit_ype varchar(20),  
area_type varchar(20),  
cust_no int,  
app_no int,  
id  varchar(20),  
Jan  varchar(20),  
Feb  varchar(20),  
Mar  varchar(20),  
Apr  varchar(20),  
May  varchar(20),  
Jun  varchar(20),  
Jul  varchar(20),  
Aug  varchar(20),  
Sep  varchar(20),  
Oct  varchar(20),  
Nov  varchar(20),  
Dec  varchar(20),  
[year] int  
)  
insert into tempppp values  
('Convey','Value','comp_bus','draft',1,2,'CK','','',0,1000,0,0,1000,0,0,500,0,500,2022),  
('Convey','Sol','comp_bus','live',12,6,'TM',0,0,0,0,6000,0,6000,0,0,4500,0,4500,2022),  
('Convey','Value','comp_bus','stuck',65,7,'PO',0,0,0,200,200,200,200,0,0,0,0,0,2022),  
('Convey','Mort','app_new','go',33,9,null,7680,0,0,0,0,0,0,0,0,0,0,0,2022)  
  
select remit, area, remit_ype, area_type, cust_no, app_no, id, [month], [value], [year]  
 from tempppp  
 unpivot  
 (  
     [value] for [month] in (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec])  
 ) upvt

Output:

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

Jingyang Li 5,896 Volunteer Moderator

   select remit, area, remit_ype, area_type, cust_no, app_no, id, [month], [value], [year]  
  from yourtablename  
  cross apply (Values ('Jan',Jan)  
  ,('Feb',Feb)   
  ,('Mar',Mar)   
  ,('Apr', Apr)  
  ,('May', May)  
  ,('Jun', Jun)  
  ,('Jul', Jul)  
  ,('Aug', Aug)  
  ,('Sept', Sept)  
  ,('Oct', Oct)  
  ,('Nov', Nov)  
  ,('Dec',Dec)  )   unpvt([Month],value)

Share via

Unpivot Data

4 answers

Your answer