Click here to Skip to main content
15,889,878 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi

I have a table with below structure

SQL
Elements 	 JAN 	FEB 	MAR 	APR 	MAY 	JUN
ABC       	100	200	300	400	500	600
XYZ       	200	300	400	500	600	700
PQR      	500	400	300	200	100	800


and I want to convert to below structure

SQL
Month	 ABC 	XYZ	PQR
JAN	100	200	500
FEB	200	300	400
MAR	300	400	300
APR	400	500	200
MAY	500	600	100
JUN	600	700	800

Which is the best method to do this
I can not create a table, i am trying to make a view

Thanks
Posted
Updated 30-Oct-14 0:13am
v2
Comments
Member 11034332 29-Oct-14 5:15am    
you may please suggest SSIS column mapping
Laiju k 30-Oct-14 6:15am    
what are you trying to accomplish and where
Member 11034332 1-Nov-14 3:08am    
SSIS Column mapping I tried
Column Mapping is confusing

@Laiju,i don't think your question is relevant

 
Share this answer
 
Comments
Maciej Los 30-Oct-14 18:55pm    
+5!
King Fisher 31-Oct-14 0:08am    
Thank you :)
 
Share this answer
 
Try This,
SQL
CREATE TABLE [dbo].[Table_1]([Elements] [nvarchar](max) NULL,
	[Jan] [float] NULL,	[feb] [float] NULL,	[mar] [float] NULL,
	[Apr] [float] NULL,	[May] [float] NULL,	[Jun] [float] NULL
) ON [PRIMARY]

GO

insert into Table_1(Elements,Jan,feb,mar,Apr,May,Jun) values('ABC',100,200,300,400,500,600),
('XYZ',200,300,400,500,600,700),
('PQR',500,400,300,200,100,800);


SELECT Elements,Jan,feb,mar,Apr ,May ,Jun FROM Table_1

select col Month1,
  sum(case when Elements = 'ABC' then value end) ABC,
  sum(case when Elements = 'XYZ' then value end) XYZ,
  SUM(case when Elements = 'PQR' then value end) PQR
from
( select Elements, Jan value, 'JAN' col  from Table_1  union all
  select Elements, feb value, 'FEB' col  from Table_1  union all 
  select Elements, mar value, 'MAR' col  from Table_1  union all
  select Elements, Apr value, 'Apr' col from Table_1   union all 
  select Elements, May value, 'MAY' col from Table_1   union all 
  select Elements, Jun value, 'JUN' col  from Table_1
) x group by col ;
 
Share this answer
 
Comments
King Fisher 31-Oct-14 1:36am    
Good Try ,Don't post Code if he Doesn't try Anything.you make him Lazy
Let him Learn . :) :)
Member 11034332 1-Nov-14 3:22am    
Thanks kskumaran,It is nice
But we need to hardcode the column and raw names here
see this Fiddle link[^] this will give you a better performance, view the execution plan to see the difference
 
Share this answer
 
v2
Thanks to all,Sorry I was away, and I got the it ,Thanks for your support

here I am posting the method I used,It is dynamic un pivot
so we don't need hard coding of column/raw name

step1.Create table and insert values

SQL
CREATE TABLE test
 ([Elements] varchar(10), [Jan] int, [Feb] int, [March] int, [April] int) ;

INSERT INTO test
 ([elements], [Jan], [Feb], [March], [April]) VALUES  ('ABC', 100, 200, 300, 400),  ('XYZ', 200, 300, 400, 500),  ('PQR', 500, 400, 300, 200) ;


Step2. The query to arrange rows to columns and vice versa

SQL
DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('test') and
               C.name <> 'Elements'
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ','
                      + quotename(Elements)
                    from test t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query
  = 'select name, '+@colsPivot+'<mailto:'+@colspivot+'>
      from
      (
        select Elements, name, value
        from test
        unpivot
        (
          value for name in ('+@colsUnpivot+'<mailto:'+@colsunpivot+'>)
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for Elements in ('+@colsPivot+'<mailto:'+@colspivot+'>)
      ) piv'

exec(@query);



Thanks
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900